Creating Unique Index with Entity Framework 6.1 fluent API

Question

I have a column "Name" that must be unqiue. No foreign key or anything like that.

EF 6.1 finally supports creating such indexes via Annotations. That has been discussed already on SO. But it seems it can only be done via annotations in the classes. How do I do that using only the Fluent API?

Something like this:

public class PersonConfiguration : EntityTypeConfiguration<Person>
{
    public PersonConfiguration()
    {
        HasKey(p => p.Id);
        Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        //not possible?
        Index(p => p.Name).IsUnique();  //???
    }
}

Gourmet

Answer 1

NOTE: Relevant to EF 6

You can use IndexAttribute as mentioned but with Fluent API instead of DataAnnotations which will do the trick:

modelBuilder 
    .Entity<Person>() 
    .Property(t => t.Name) 
    .HasColumnAnnotation( 
        "Index",  
        new IndexAnnotation(new IndexAttribute("IX_Name") { IsUnique = true }));

Unfortunately there is no other way to create unique indexes using Fluent API. There is an open issue regarding this feature: Unique Constraints (Unique Indexes)

---

UPDATE: Entity Framework Core

In the latest EF Core release you can rely on Fluent API to specify indexes without additional tricks.
HasIndex allows to define it:

modelBuilder 
    .Entity<Person>()
    .HasIndex(x => x.Name);

Hence it returs IndexBuilder object you can use it for further index configurations (i.e uniqueness):

modelBuilder 
    .Entity<Person>()
    .HasIndex(x => x.Name)
    .IsUnique();

Ictinus

This is what I was looking for. Not as simple as hoped but it works. Thanks. – Gourmet

Answer 2

Based on Anatolii's answer, 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" });
    }
}

Crucifix

Is it possible to maintain the fluent chain, or does IsUnique() have to be last? Also, for use in custom conventions, your same approach can be used with the type ConventionPrimitivePropertyConfiguration rather than PrimitivePropertyConfiguration. – Resume

The fluent chain can be maintained with StringPropertyConfiguration. – Resume

Answer 3

This answer contains only additional information as there are already great answers.

If you want to have multiple unique fields in your index, you can achieve it by adding the Order property. You must also make sure that you use the same index name in all your properties (see uniqueIndex below).

string uniqueIndex = "UN_TableName";

this.Property(t => t.PropertyOne)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName,
        new IndexAnnotation(
            new IndexAttribute(uniqueIndex)
            {
                IsUnique = true,
                Order = 1
            }
        )
    );

this.Property(t => t.PropertyTwo)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName,
        new IndexAnnotation(
            new IndexAttribute(uniqueIndex)
            {
                IsUnique = true,
                Order = 2
            }

        )
    );

Now, let's say you also want an index on PropertyTwo.

The wrong way of doing it would be to start a new line with this.Property(t => t.PropertyTwo) as it would CANCEL your unique index.

All your indexes (index and unique) must be defined at the same time. This would be the right way to do it :

this.Property(t => t.PropertyTwo)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName,
        new IndexAnnotation(new[] 
            {
                new IndexAttribute(), // This is the index
                new IndexAttribute(uniqueIndex) // This is the Unique made earlier
                {
                    IsUnique = true,
                    Order = 2
                }
            }
        )
    );

Finally, if you also want to change the sort order of your index/unique, you can see :

How to add an index on multiple columns with ASC/DESC sort using the Fluent API ?.

Cykana

is ASC/DESC sort available with 6.2 now ? And also your second example, how would one do it with HasIndex method in 6.2? – Circumspect

Answer 4

In the new version of EF that is 6.2 you can use HasIndex method:

 modelBuilder.Entity<User>().HasIndex(user => user.Email).IsUnique(true);
 modelBuilder.Entity<User>().HasIndex(user => user.UserName).IsUnique(true);

Update For EF Core:

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

 modelBuilder.Entity<User>()
        .HasIndex(b => b.UserName)
        .IsUnique();

Microsoft Docs - EF Core Indexes

Paragrapher

Answer 5

You can create a unique index in your Migration file.

In Package Manager:

1. Enable-Migrations
2. Add-Migration InitialMigration

This will create a new file in your Migrations folder that is timestamped. The class will have an Up and a Down method that you can use to create the index:

public override void Up()
{
    // ... 
    CreateIndex("tableName", "columnName", unique: true, name: "myIndex");
}

public override void Down()
{
    // ...
    DropIndex("tableName", "myIndex");
}

To run the migration type Update-Database in the package manager.

You can also add the index as part of the migration that creates the table as well:

CreateTable(
    "dbo.Products",
    c => new
        {
            ProductId = c.Int(nullable: false, identity: true),
            ProductName = c.String(nullable: false, maxLength: 256),
        })
    .Index(c => c.ProductName, name: "IX_Products_ProductName", unique: true)
    .PrimaryKey(t => t.ProductId);

Brython

Answer 6

For me, using MVC 5 and EF 6, the key point was specifying a length on the string property where I wanted to place the index.

protected override void OnModelCreating(DbModelBuilder modelBuilder){
    //More stuff
    modelBuilder.Entity<Device>().Property(c => c.Name).HasColumnType("nvarchar").HasMaxLength(50);
    modelBuilder.Entity<Device>().HasIndex(c => c.Name).IsUnique(true); 
}

Lounge