Setting unique Constraint with fluent API?

I'm trying to build an EF Entity with Code First, and an EntityTypeConfiguration using fluent API. creating primary keys is easy but not so with a Unique Constraint. I was seeing old posts that suggested executing native SQL commands for this, but that seem to defeat the purpose. is this possible with EF6?

Solution 1:

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 }));

Solution 2:

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; }
    }
}

Solution 3:

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