Unique Key constraints for multiple columns in Ent

问题:

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:

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

回答2:

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.

回答3:

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 => t.Email)
    .HasColumnName("Email")
    .HasMaxLength(250)
    .IsRequired()
    .HasUniqueIndexAnnotation("UQ_User_EmailPerApplication", 0);

this.Property(t => 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
)

回答4:

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<Entity>().HasKey(x => new { x.FirstColumn, x.SecondColumn });

回答5:

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<Employee>
{
    public EmployeeMap ()
    {
        ToTable("Employee");

        Property(p => p.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

        Property(p => p.Name)
            .HasUniqueIndexAnnotation("UK_Employee_Name_Company", 0);
        Property(p => p.CompanyId )
            .HasUniqueIndexAnnotation("UK_Employee_Name_Company", 1);
        HasRequired(p => p.Company)
            .WithMany()
            .HasForeignKey(p => p.CompanyId)
            .WillCascadeOnDelete(false);
    }
}

Note that I also used @niaher extension for unique index annotation.

回答6:

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 => e.FirstColumn == value1 
                           && 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.

回答7:

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<ClassName>()
            .HasIndex(a => new { a.Column1, a.Column2}).IsUnique();