How to specify a multi-column UNIQUE constraint in code-first Entity Framework fluent API

Question

Suppose I have the following entities:

public class Calendar{
    public int ID{get;set;}
    public ICollection<Day> Days { get; set; }
}
public class Day{
    public int ID{get;set;}
    public DateTime Date{get;set;}
    public int CalendarID{get;set;}
}

This is a one-to-many relationship with CalendarID as a foreign key. The issue is I also want to make sure that each date exists only once in each calendar. That is, no two days have both the same Date and same CalendarID. In raw SQL I can do this by:

ALTER TABLE Days
ADD CONSTRAINT UN_Day UNIQUE ([Date],CalendarID);

However, Entity Framework won't know I want this when it creates the table automatically. How can I specify this in the fluent API?

SpruceMoose · Accepted Answer · 2017-12-08 17:04:11Z

5

See Configuring an Index on one or more properties.

In your mapping class, assuming it extends from EntityTypeConfiguration<Day> then you will need to add using System.Data.Entity.Infrastructure.Annotations; and do the following:

this.Property(x => x.Date)
    .HasColumnAnnotation(IndexAnnotation.AnnotationName,
                 new IndexAnnotation(new IndexAttribute("UN_Day", 0) { IsUnique = true }));

this.Property(x => x.CalendarID)
    .HasColumnAnnotation(IndexAnnotation.AnnotationName,
                 new IndexAnnotation(new IndexAttribute("UN_Day", 1) { IsUnique = true }));

edited Dec 8, 2017 at 17:04

answered Dec 8, 2017 at 16:52

SpruceMoose

10.4k4 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Konrad Viltersten Over a year ago

I don't have HasColumnAnnotation() but I see HasAnnotation(). Is that a newer version of the same method? I wasn't even able to import the namespace you mentioned. Apparently, already Entity isn't found in System.Data.Entity.Infrastructure.Annotations. I checked different NuGet packages but didn't recognize anything obvious. Given that your answer is 3+ years old, I suspect that the syntax might have changed a bit. +1 anyway but I'd be delighted to get more current info too, if you have it.

SpruceMoose Over a year ago

HasAnnotation looks like the EF Core equivalent to HasColumnAnnotation

Collectives™ on Stack Overflow

How to specify a multi-column UNIQUE constraint in code-first Entity Framework fluent API

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related