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?

Answer 1

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 }));