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();  //???
    }
}

Solution 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

Fluent API to specify indexes

HasIndex

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

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

Solution 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 ?.

Solution 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

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

View more solutions

Comments

[John]

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

[osoviejo]

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.

[osoviejo]

The fluent chain can be maintained with StringPropertyConfiguration.

[kuldeep]

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?