How to create index in Entity Framework 6.2 with code first

entity-framework entity-framework-6 ef-code-first

99,640

Solution 1

Well 26.10.2017 Entity Framework 6.2 was officially released. It includes a possibility to define indexes with ease via Fluent API. Ho it is to use was already announced in the beta of 6.2.

Now you can use the HasIndex() method, followed by IsUnique() if it should be an unique index.

Just a small comparison (before/after) example:

// before 
modelBuilder.Entity<Person>()
        .Property(e => e.Name)
        .HasColumnAnnotation(
            IndexAnnotation.AnnotationName, 
            new IndexAnnotation(new IndexAttribute { IsUnique = true }));

// after
modelBuilder.Entity<Person>()
    .HasIndex(p => p.Name)
    .IsUnique();

// multi column index
modelBuilder.Entity<Person>()
    .HasIndex(p => new { p.Name, p.Firstname })
    .IsUnique();

It is also possible to mark the index as clustered with .IsClustered().

EDIT #1

Added an example for multi column index and additional information how to mark an index as clustered.

EDIT #2

As additional information, in EF Core 2.1 it is exactly the same like in EF 6.2 now.
Here is the MS Doc artcile as reference.

Solution 2

Currently there is no "first class support" for creating a index via the fluent API, but what you can do is via the fluent API you can mark properties as having attributes from the Annotation API. This will allow you to add the Index attribute via a fluent interface.

Here are some examples from the work item from Issues site for EF.

Create a index on a single column:

modelBuilder.Entity<MyEntity>()
    .Property(e => e.MyProperty)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName, 
        new IndexAnnotation(new IndexAttribute()));

Multiple indexes on a single column:

modelBuilder.Entity<MyEntity>()
    .Property(e => e.MyProperty)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName, 
        new IndexAnnotation(new[]
            {
                new IndexAttribute("Index1"),
                new IndexAttribute("Index2") { IsUnique = true }
            }));

Multi-Column indexes:

modelBuilder.Entity<MyEntity>()
    .Property(e => e.MyProperty1)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName,
        new IndexAnnotation(new IndexAttribute("MyIndex", 1)));

modelBuilder.Entity<MyEntity>()
    .Property(e => e.MyProperty2)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName, 
        new IndexAnnotation(new IndexAttribute("MyIndex", 2)));

Using the above techniques will cause .CreateIndex() calls to be automatically created for you in your Up() function when you scaffold your next migration (or be automatically created in the database if you are not using migrations).

Solution 3

I've created a some extension methods and wrapped them in a nuget package to make this much easier.

Install the EntityFramework.IndexingExtensions nuget package.

Then you can do the following:

public class MyDataContext : DbContext
{
  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
    modelBuilder.Entity<Customer>()
        .HasIndex("IX_Customers_Name",          // Provide the index name.
            e => e.Property(x => x.LastName),   // Specify at least one column.
            e => e.Property(x => x.FirstName))  // Multiple columns as desired.

        .HasIndex("IX_Customers_EmailAddress",  // Supports fluent chaining for more indexes.
            IndexOptions.Unique,                // Supports flags for unique and clustered.
            e => e.Property(x => x.EmailAddress)); 
  }
}

The project and source code are here. Enjoy!

Solution 4

Without an explicit name:

[Index]
public int Rating { get; set; }

With a specific name:

[Index("PostRatingIndex")] 
public int Rating { get; set; }

Solution 5

From EF 6.1 onward the attribute [Index] is supported.
Use [Index(IsUnique = true)] for unique index.
Here is the link from Microsoft

public class User 
{ 
    public int UserId { get; set; } 

    [Index(IsUnique = true)] 
    [StringLength(200)] 
    public string Username { get; set; } 

    public string DisplayName { get; set; } 
}

View more solutions

99,640

Author by

Valo

Experienced hands-on architect with a proven track record of delivering complex financial and other software systems, taking them throughout the full software development lifecycle. Interested in architecting and developing distributed, in-house and cloud-based, microservices/SOA systems, object-oriented methods, programming best practices, software development methodologies. Tech stack: .NET, C#, PowerShell, LINQ, Entity Framework, NHibernate, SQL Server, HP Vertica, Unity, Enterprise Library (DDD, DI and AOP), WCF, microservices/SOA, multithreaded and asynchronous programming, application and data security, WPF (MVVM), ASP.NET (MVC and Web API), JSON, XML, FIX/FIXML, C/C++.

Updated on December 10, 2020

Comments

Valo over 3 years

Is there a way to create an index on a property/column using code-first, instead of using the new IndexAttribute ?
Valo about 10 years

Sure, Mert. At the moment I am using migrations and there in the Up() method you can also put: CreateIndex("dbo.Table1", "Column1", true, "Column1_IX") and in Down() DropIndex(("dbo.Table1", "Column1_IX"). I was just hoping that they added a fluent API too...
Joy almost 10 years

that might add the index on the column but that wont remove the clustered index created on primary key . The hasKey creates the clustered index on primary keys which aren't by default gets removed. That has to be explicitly removed from the migration file created by stating clusered:false in .Primarykey(x=>x.id,clustered:false) method
dunwan about 9 years

Maximum key length is 900 bytes for nvarchar and 450 bytes for varchar. If you are using code first the string properties will be nvarchar and you should include the attribute "StringLength" as in [[StringLength(450)]
Mixxiphoid almost 9 years

I really like the package but it seems the index name is sometimes missing after scaffolding in the up script. It only appeared for me when using 4 or more properties in my index. I'm working with EF 6.1.3.
Matt Johnson-Pint almost 9 years

@Mixxiphoid - would you please log the issue here with supporting details? Also be sure you have version 1.0.1, since there was a bug in 1.0.0.
Mixxiphoid almost 9 years

I do have version 1.0.1. I will log the issue but cannot do so at this moment.
Hakan Fıstık over 8 years

I tried the HasAnnotation method and there is NO method like this. but I found a method which name HasColumnAnnotation which accepts the parameters which you provide. Do you need to update your answer or am I wrong?
Scott Chamberlain about 8 years

@HakamFostok I took the example directly from the EF site. Perhaps the name changed in one of the versions or there is a typo in the original version.
Zac Charles about 8 years

See right down the bottom of the following link from a design meeting earlier this year: "Rename HasAnnotation to HasColumnAnnotation (plus other relevant places in the code base).". entityframework.codeplex.com/…
Enamul Hassan almost 8 years

Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference.
Darie Dorlus over 7 years

@manetsus Very well. I added a code snippet to reflect the change.
Hamed Zakery Miab over 7 years

Index seems to be depricated :(
Hugo Hilário over 7 years

@HamedZakeryMiab Which version of Entity Framework are you using? Index was not deprecated.
Hamed Zakery Miab over 7 years

excuse me, I forgot to include EntityFramework. it's included in that assembly. just confused about the NS.
AlbatrossCafe over 7 years

@HamedZakeryMiab yeah that was super confusing! I thought it was part of System.DataAnnotations! It is definitely the entity framework package
Hakan Fıstık about 7 years

the question contains the following statement instead of using the new IndexAttribute did you notice it ?
GDroid about 7 years

How do I add index participating column order to descending? By default .HasIndex("IX_Customers_EmailAddress", IndexOptions.Unique, ... creates ascending order for all participating columns in index.
Matt Johnson-Pint about 7 years

@GDroid - Unfortunately, this isn't exposed by EF's IndexAttribute class, so I cannot include it in my library.
andrew pate almost 7 years

The string length is needed otherwise you see a 'is of a type that is invalid for use as a key column in an index' exception. My collegue prefers the modelBuilder solution on the Conntext so your not cluttering up your User class, which I guess is valid.
Valo over 6 years

This is great! I suppose that if I have multicolumn index it would be something like: .HasIndex(p => new {p.Name, p.Xyz})
ChW over 6 years

Oh, sorry, sure. It should be new. I will fix it.
user3417479 over 5 years

Could you please show how can we write the same code in Core 2.x?
ChW over 5 years

As far as I know it should be the same code like shown under "after" and "multi column index".
enorl76 about 5 years

What about indexes with multiple columns for uniqueness? Quite common to have a multi-column Unique Key index...
Darie Dorlus about 5 years

@enorl76 That is also supported. For each columns you would need to use an attribute like the following, [Index("IX_BlogIdAndRating", 2)] public int Rating { get; set; } [Index("IX_BlogIdAndRating", 1)] public int BlogId { get; set; } Here the reference from Microsoft
enorl76 about 5 years

@DarieDorlus yes thank you. Basically you're saying as long as IsUnique=true on at least one of the Index attributes, and on all the Index attributes that the index name matches, the columns will get added into a unique-key constraint index. This is what I ended up finding out locally.
Chris Schaller over 4 years

As of EF 6.1, this is the correct answer. docs.microsoft.com/en-us/ef/ef6/modeling/code-first/…
Karthic G over 3 years

Hi I want to add HasIndex methos using Roslyn part, Can you help to do ?