Unique Key constraints for multiple columns in Entity Framework

Question

I m using Entity Framework 5 0 Code First   public class Entity        Key  DatabaseGenerated DatabaseGeneratedOption Identity      public string EntityId   get  set      public int FirstColumn    get  set      public int SecondColumn    get  set        I want to make the combination between FirstColumn and SecondColumn as unique   Example   Id  FirstColumn  SecondColumn  1       1              1         OK 2       2              1         OK 3       3              3         OK 5       3              1         THIS OK  4       3              3         GRRRRR  HERE ERROR   Is there anyway to do that

User · Answer

You need to define a composite key   With data annotations it looks like this   public class Entity       public string EntityId   get  set       Key      Column Order 0      public int FirstColumn    get  set       Key      Column Order 1      public int SecondColumn    get  set        You can also do this with modelBuilder when overriding OnModelCreating by specifying   modelBuilder Entity lt Entity gt    HasKey x   gt  new   x FirstColumn  x SecondColumn

User · Answer

The answer from niaher stating that to use the fluent API you need a custom extension may have been correct at the time of writing   You can now  EF core 2 1  use the fluent API as follows   modelBuilder Entity lt ClassName gt                 HasIndex a   gt  new   a Column1  a Column2   IsUnique

User · Answer

I found three ways to solve the problem   Unique indexes in EntityFramework Core   First approach   protected override void OnModelCreating ModelBuilder modelBuilder       modelBuilder Entity lt Entity gt        HasIndex p   gt  new  p FirstColumn   p SecondColumn   IsUnique        The second approach to create Unique Constraints with EF Core by using Alternate Keys   Examples  One column   modelBuilder Entity lt Blog gt    HasAlternateKey c   gt  c SecondColumn  HasName  IX SingeColumn      Multiple columns   modelBuilder Entity lt Entity gt    HasAlternateKey c   gt  new     c FirstColumn  c SecondColumn   HasName  IX MultipleColumns      EF 6 and below     First approach   dbContext Database ExecuteSqlCommand string Format                            CREATE UNIQUE INDEX LX  0  ON  0    1                                        Entitys    FirstColumn  SecondColumn       This approach is very fast and useful but the main problem is that Entity Framework doesn t know anything about those changes     Second approach    I found it in this post but I did not tried by myself   CreateIndex  Entitys   new string 2     FirstColumn    SecondColumn                   true   IX Entitys      The problem of this approach is the following  It needs DbMigration so what do you do if you don t have it     Third approach     I think this is the best one but it requires some time to do it  I will just show you the idea behind it    In this link http   code msdn microsoft com CSASPNETUniqueConstraintInE-d357224a    you can find the code for unique key data annotation    UniqueKey     Unique Key  public int FirstColumn    get  set    UniqueKey     Unique Key  public int SecondColumn    get  set       The problem hier 1  1    OK  1  2    NO OK 1 IS UNIQUE   The problem for this approach  How can I combine them  I have an idea to extend this Microsoft implementation for example    UniqueKey  1     Unique Key  public int FirstColumn    get  set    UniqueKey  1     Unique Key  public int SecondColumn    get  set     Later in the IDatabaseInitializer as described in the Microsoft example you can combine the keys according to the given integer  One thing has to be noted though  If the unique property is of type string then you have to set the MaxLength

User · Answer

If you re using Code-First  you can implement a custom extension HasUniqueIndexAnnotation  using System ComponentModel DataAnnotations Schema  using System Data Entity Infrastructure Annotations  using System Data Entity ModelConfiguration Configuration   internal static class TypeConfigurationExtensions       public static PrimitivePropertyConfiguration HasUniqueIndexAnnotation          this PrimitivePropertyConfiguration property           string indexName          int columnOrder                var indexAttribute   new IndexAttribute indexName  columnOrder    IsUnique   true            var indexAnnotation   new IndexAnnotation indexAttribute            return property HasColumnAnnotation IndexAnnotation AnnotationName  indexAnnotation             Then use it like so   this Property t   gt  t Email       HasColumnName  Email        HasMaxLength 250       IsRequired        HasUniqueIndexAnnotation  UQ User EmailPerApplication   0    this Property t   gt  t ApplicationId       HasColumnName  ApplicationId        HasUniqueIndexAnnotation  UQ User EmailPerApplication   1     Which will result in this migration   public override void Up         CreateIndex  dbo User   new      Email    ApplicationId     unique  true  name   UQ User EmailPerApplication       public override void Down         DropIndex  dbo User    UQ User EmailPerApplication        And eventually end up in database as   CREATE UNIQUE NONCLUSTERED INDEX  UQ User EmailPerApplication  ON  dbo   User         Email  ASC       ApplicationId  ASC

User · Answer

Completing  chuck answer for using composite indices with foreign keys   You need to define a property that will hold the value of the foreign key  You can then use this property inside the index definition   For example  we have company with employees and only we have a unique constraint on  name  company  for any employee   class Company       public Guid Id   get  set       class Employee       public Guid Id   get  set         Required      public String Name   get  set        public Company Company    get  set         Required      public Guid CompanyId   get  set        Now the mapping of the Employee class   class EmployeeMap   EntityTypeConfiguration lt Employee gt        public EmployeeMap                  ToTable  Employee             Property p   gt  p Id               HasDatabaseGeneratedOption DatabaseGeneratedOption None            Property p   gt  p Name               HasUniqueIndexAnnotation  UK Employee Name Company   0           Property p   gt  p CompanyId                HasUniqueIndexAnnotation  UK Employee Name Company   1           HasRequired p   gt  p Company               WithMany                HasForeignKey p   gt  p CompanyId               WillCascadeOnDelete false             Note that I also used  niaher extension for unique index annotation

User · Answer

In the accepted answer by  chuck  there is a comment saying it will not work in the case of FK   it worked for me  case of EF6  Net4 7 2  public class OnCallDay        public int Id   get  set           Key       Index  IX OnCallDateEmployee   1  IsUnique   true       public DateTime Date   get  set         ForeignKey  Employee         Index  IX OnCallDateEmployee   2  IsUnique   true       public string EmployeeId   get  set        public virtual ApplicationUser Employee  get  set

User · Answer

I assume you always want EntityId to be the primary key  so replacing it by a composite key is not an option  if only because composite keys are far more complicated to work with and because it is not very sensible to have primary keys that also have meaning in the business logic    The least you should do is create a unique key on both fields in the database and specifically check for unique key violation exceptions when saving changes   Additionally you could  should  check for unique values before saving changes  The best way to do that is by an Any   query  because it minimizes the amount of transferred data   if  context Entities Any e   gt  e FirstColumn    value1                              amp  amp  e SecondColumn    value2            deal with duplicate values here      Beware that this check alone is never enough  There is always some latency between the check and the actual commit  so you ll always need the unique constraint   exception handling

User · Answer

With Entity Framework 6 1  you can now do this    Index  IX FirstAndSecond   1  IsUnique   true   public int FirstColumn   get  set      Index  IX FirstAndSecond   2  IsUnique   true   public int SecondColumn   get  set      The second parameter in the attribute is where you can specify the order of the columns in the index  More information  MSDN

User · Answer

Recently added a composite key with the uniqueness of 2 columns using the approach that  chuck  recommended  thank  chuck  Only this approached looked cleaner to me   public int groupId  get  set     Index  IX ClientGrouping   1  IsUnique   true   public int ClientId   get  set      Index  IX ClientGrouping   2  IsUnique   true   public int GroupName   get  set

[entity-framework] Unique Key constraints for multiple columns in Entity Framework

Examples related to entity-framework

Examples related to ef-code-first

Examples related to constraints

Examples related to multiple-columns

Examples related to unique-key