asp.net core create a constraint on two columns, one of which is another entity

Question

I have 2 classes (entities) Exemption and Period, and I want to make a unique index with IdNumber from exemption and Id from Period

     public class Exemption
        {
            [Key]
            public int Id { get; set; }
            [Required]
            public string FirstName { get; set; }
            .
            .
            .
            [Required]
            public string IdNumber { get; set; }
            [Required]
            public Period Period { get; set; }
         }

     public class Period
        {
            [Key]
            public int Id { get; set; }
            .
            .
            .
            [Required]
            public string Year { get; set; }

         }

Using the Fluent API I added .HasIndex inside the OnModelCreating but got an error

builder.Entity<Exemption>().HasIndex(c => new { c.IdNumber, c.Period }).IsUnique();

got the following error when adding migration

The properties expression 'c => new <>f__AnonymousType5`2(IdNumber = c.IdNumber, Id = c.Period.Id)' is not valid. The expression should represent a property access: 't => t.MyProperty'. When specifying multiple properties use an anonymous type: 't => new {  t.MyProperty1, t.MyProperty2 }'.

i tried adding the c.Period.Id

 builder.Entity<Exemption>()
                    .HasIndex(c => new { c.IdNumber, c.Period.Id }).IsUnique();

got the following error

The property 'Period' cannot be added to the entity type 'Exemption' because a navigation property with the same name already exists on entity type 'Exemption'.

I need to allow only one Exemption per IdNumber per Period.Id how do I do that in EF Core 1.0 ?

Answer 1

You will need to explicitly add the foreign key:

public class Exemption
{
   [Key]
   public int Id { get; set; }
   [Required]
   public string FirstName { get; set; }
   .
   .
   .
   [Required]
   public string IdNumber { get; set; }
   [Required]
   public int PeriodId { get; set; }
   [ForeignKey("PeriodId")]
   public Period Period { get; set; }
}

Then you can index it:

builder.Entity<Exemption>().HasIndex(c => new { c.IdNumber, c.PeriodId }).IsUnique();

Also, since you are using fluent api code for the index, you could use fluent for all the other annotations as well. For example:

builder.Entity<Exemption>()
       .HasKey(c => c.Id)
       .HasIndex(c => new { c.IdNumber, c.PeriodId })
       .IsUnique();

builder.Entity<Exemption>().Property(p => p.FirstName)
       .IsRequired()          
       .HasMaxLength(100);