entity-framework-corecomposite-key

Entity Framework Core: is there a way to say an entry is unique across 2 column values?


My question is in the context of Entity Framework Core talking to SQL database.

I have a class as follows:

public class County
{
        public int Id { get; set; }
        public string Name { get; set; }
        public int StateId { get; set; }
        public State State { get; set; }
}

I have one of these records for each county in each state. The Name is not unique because there is an Orange County in both California and Florida. However, the combination of Name + StateId is unique.

Is there a way to specify this in Entity Framework Core so that it tells the SQL database to enforce that uniqueness? And Id should remain the PK so I don't want to make the Name+StateId a composite primary key.


Solution

  • Sure -

    Your example is Code First.

    You can do something like this:

    public class County
    {
        [Key]
        public int Id { get; set; }
        [Index("IX_Location", 2, IsUnique = true)]
        public string Name { get; set; }
        [Index("IX_Location", 3, IsUnique = true)]
        public int StateId { get; set; }
        public State State { get; set; }
    }
    

    "Id" remains your primary key; your table has a clustered index built on Id. But you simply create a second index, for your "dual-column uniqueness constraint". On whichever columns you want (e.g. Name + StateId).


    Per David, here is alternative syntax, using Fluent API/LINQ:

    modelBuilder.Entity<County>()
        .HasIndex(e => new { e.Name, e.StateId })
        .IsUnique();