Unique Key constraints for multiple columns in Entity Framework

constraints ef-code-first entity-framework multiple-columns unique-key

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?

1
233
2/17/2015 2:50:42 PM

Accepted 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

362
5/6/2014 5:05:01 PM

Popular Answer

I found three ways to solve the problem.

Unique indexes in EntityFramework Core:

First approach:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   modelBuilder.Entity<Entity>()
   .HasIndex(p => 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<Blog>().HasAlternateKey(c => c.SecondColumn).HasName("IX_SingeColumn");

Multiple columns:

modelBuilder.Entity<Entity>().HasAlternateKey(c => 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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow