c# - Setting unique Constraint with fluent API?

ID : 20369

viewed : 26

Tags : c#entity-frameworkentity-framework-6ef-fluent-apic#

Top 5 Answer for c# - Setting unique Constraint with fluent API?

vote vote

95

On EF6.2, you can use HasIndex() to add indexes for migration through fluent API.

https://github.com/aspnet/EntityFramework6/issues/274

Example

modelBuilder     .Entity<User>()     .HasIndex(u => u.Email)         .IsUnique(); 

On EF6.1 onwards, you can use IndexAnnotation() to add indexes for migration in your fluent API.

http://msdn.microsoft.com/en-us/data/jj591617.aspx#PropertyIndex

You must add reference to:

using System.Data.Entity.Infrastructure.Annotations; 

Basic Example

Here is a simple usage, adding an index on the User.FirstName property

modelBuilder      .Entity<User>()      .Property(t => t.FirstName)      .HasColumnAnnotation(IndexAnnotation.AnnotationName, new IndexAnnotation(new IndexAttribute())); 

Practical Example:

Here is a more realistic example. It adds a unique index on multiple properties: User.FirstName and User.LastName, with an index name "IX_FirstNameLastName"

modelBuilder      .Entity<User>()      .Property(t => t.FirstName)      .IsRequired()     .HasMaxLength(60)     .HasColumnAnnotation(         IndexAnnotation.AnnotationName,          new IndexAnnotation(             new IndexAttribute("IX_FirstNameLastName", 1) { IsUnique = true }));  modelBuilder      .Entity<User>()      .Property(t => t.LastName)      .IsRequired()     .HasMaxLength(60)     .HasColumnAnnotation(         IndexAnnotation.AnnotationName,          new IndexAnnotation(             new IndexAttribute("IX_FirstNameLastName", 2) { IsUnique = true })); 
vote vote

86

As an addition to Yorro's answer, it can also be done by using attributes.

Sample for int type unique key combination:

[Index("IX_UniqueKeyInt", IsUnique = true, Order = 1)] public int UniqueKeyIntPart1 { get; set; }  [Index("IX_UniqueKeyInt", IsUnique = true, Order = 2)] public int UniqueKeyIntPart2 { get; set; } 

If the data type is string, then MaxLength attribute must be added:

[Index("IX_UniqueKeyString", IsUnique = true, Order = 1)] [MaxLength(50)] public string UniqueKeyStringPart1 { get; set; }  [Index("IX_UniqueKeyString", IsUnique = true, Order = 2)] [MaxLength(50)] public string UniqueKeyStringPart2 { get; set; } 

If there is a domain/storage model separation concern, using Metadatatype attribute/class can be an option: https://msdn.microsoft.com/en-us/library/ff664465%28v=pandp.50%29.aspx?f=255&MSPPError=-2147217396


A quick console app example:

using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; using System.Data.Entity;  namespace EFIndexTest {     class Program     {         static void Main(string[] args)         {             using (var context = new AppDbContext())             {                 var newUser = new User { UniqueKeyIntPart1 = 1, UniqueKeyIntPart2 = 1, UniqueKeyStringPart1 = "A", UniqueKeyStringPart2 = "A" };                 context.UserSet.Add(newUser);                 context.SaveChanges();             }         }     }      [MetadataType(typeof(UserMetadata))]     public class User     {         public int Id { get; set; }         public int UniqueKeyIntPart1 { get; set; }         public int UniqueKeyIntPart2 { get; set; }         public string UniqueKeyStringPart1 { get; set; }         public string UniqueKeyStringPart2 { get; set; }     }      public class UserMetadata     {         [Index("IX_UniqueKeyInt", IsUnique = true, Order = 1)]         public int UniqueKeyIntPart1 { get; set; }          [Index("IX_UniqueKeyInt", IsUnique = true, Order = 2)]         public int UniqueKeyIntPart2 { get; set; }          [Index("IX_UniqueKeyString", IsUnique = true, Order = 1)]         [MaxLength(50)]         public string UniqueKeyStringPart1 { get; set; }          [Index("IX_UniqueKeyString", IsUnique = true, Order = 2)]         [MaxLength(50)]         public string UniqueKeyStringPart2 { get; set; }     }      public class AppDbContext : DbContext     {         public virtual DbSet<User> UserSet { get; set; }     } } 
vote vote

77

Here is an extension method for setting unique indexes more fluently:

public static class MappingExtensions {     public static PrimitivePropertyConfiguration IsUnique(this PrimitivePropertyConfiguration configuration)     {         return configuration.HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute { IsUnique = true }));     } } 

Usage:

modelBuilder      .Entity<Person>()      .Property(t => t.Name)     .IsUnique(); 

Will generate migration such as:

public partial class Add_unique_index : DbMigration {     public override void Up()     {         CreateIndex("dbo.Person", "Name", unique: true);     }      public override void Down()     {         DropIndex("dbo.Person", new[] { "Name" });     } } 

Src: Creating Unique Index with Entity Framework 6.1 fluent API

vote vote

63

@coni2k 's answer is correct however you must add [StringLength] attribute for it to work otherwise you will get an invalid key exception (Example bellow).

[StringLength(65)] [Index("IX_FirstNameLastName", 1, IsUnique = true)] public string FirstName { get; set; }  [StringLength(65)] [Index("IX_FirstNameLastName", 2, IsUnique = true)] public string LastName { get; set; } 
vote vote

52

Unfortunately this is not supported in Entity Framework. It was on the roadmap for EF 6, but it got pushed back: Workitem 299: Unique Constraints (Unique Indexes)

Top 3 video Explaining c# - Setting unique Constraint with fluent API?

Related QUESTION?