EF 6.1: Creating indexes with IndexAttribute

Since EF 4.3 it has been possible to use CreateIndex and DropIndex in Code First Migrations to create and drop indexes. However this had to be done manually by editing the migration because the index was not included anywhere in the Code First model. Now with EF 6.1 it is possible to add index specifications to the model such that creating and dropping indexes can be handled automatically by Migrations.

Single column indexes

Consider a simple Blog entity:

public class Blog
{
    public int Id { get; set; }
    public string Title { get; set; }
    public int Rating { get; set; }
    public virtual ICollection<Post> Posts { get; set; }
}

Let’s assume this entity is already in our model and migrations have been created and applied so the model and database are both up-to-date. The easiest way to add an index is to place IndexAttribute onto a property. For example, let’s add an index to the column mapped to by the Rating property:

public class Blog
{
    public int Id { get; set; }
    public string Title { get; set; }

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

    public virtual ICollection<Post> Posts { get; set; }
}

After doing this using Add-Migration will scaffold a migration something like this:

public partial class Two : DbMigration
{
    public override void Up()
    {
        CreateIndex("dbo.Blogs", "Rating");
    }

    public override void Down()
    {
        DropIndex("dbo.Blogs", new[] { "Rating" });
    }
}

The index is being created with a default name and default options. The defaults are as follows:

  • Name: IX_[column_name]
  • Not unique
  • Not clustered

You can also use IndexAttribute to give the index a specific name and options. For example, let’s add a name to the index for the Rating column:

public class Blog
{
    public int Id { get; set; }
    public string Title { get; set; }

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

    public virtual ICollection<Post> Posts { get; set; }
}

Scaffolding another migration for this change results in:

public partial class Three : DbMigration
{
    public override void Up()
    {
        RenameIndex(table: "dbo.Blogs", name: "IX_Rating", newName: "RatingIndex");
    }

    public override void Down()
    {
        RenameIndex(table: "dbo.Blogs", name: "RatingIndex", newName: "IX_Rating");
    }
}

Notice that Migrations has scaffolded a rename for the index from the default name to the new name.

Multiple column indexes

Indexes that span multiple columns can also be scaffolded by using the same index name on multiple properties. For example:

public class Blog
{
    [Index("IdAndRating", 1)]
    public int Id { get; set; }

    public string Title { get; set; }

    [Index("RatingIndex")]
    [Index("IdAndRating", 2, IsUnique = true)]
    public int Rating { get; set; }

    public virtual ICollection<Post> Posts { get; set; }
}

Notice that the order of columns in the index is also specified. The unique and clustered options can be specified in one or all IndexAttributes. If these options are specified on more than one attribute with a given name then they must match.

Scaffolding a migration for this change results in:

public partial class Four : DbMigration
{
    public override void Up()
    {
        CreateIndex("dbo.Blogs", new[] { "Id", "Rating" }, unique: true, name: "IdAndRating");
    }

    public override void Down()
    {
        DropIndex("dbo.Blogs", "IdAndRating");
    }
}

Index conventions

The ForeignKeyIndexConvention Code First convention causes indexes to be created for the columns of any foreign key in the model unless these columns already have an index specified using IndexAttribute. If you don’t want indexes for your FKs you can remove this convention:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Conventions.Remove<ForeignKeyIndexConvention>();
}

What IndexAttribute doesn’t do

IndexAttribute can be used to create a unique index in the database. However, this does not mean that EF will be able to reason about the uniqueness of the column when dealing with relationships, etc. This feature usually referred to as support for “unique constraints” which can be voted for as a feature suggestion and on the CodePlex work item.

About Arthur Vickers

Developer on the Entity Framework team at Microsoft.
This entry was posted in Code First, Code First Migrations, Data Annotations, Entity Framework and tagged , , , , , . Bookmark the permalink.

19 Responses to EF 6.1: Creating indexes with IndexAttribute

  1. El Conde says:

    Hello!
    In which library is defined ‘index’. Not found. into system.ComponentModel.DataAnnotations

    Thanks

    • It’s in the EntityFramework assembly in the EF 6.1 NuGet package–currently available as a prerelease beta package. It will likely be moved out of the EntityFramework assembly in the future using type forwarding to another assembly similarly to what was done with other data annotations.

      • KFR42 says:

        Specifically what do I need to include? I have the 6.1 package installed and referenced in my project and it still isn’t found. I’m sure I’m missing a using statement, but I have no idea what that should be. Thanks.

      • KFR42 says:

        Don’t worry, I found it.

        You need to add ‘using System.ComponentModel.DataAnnotations.Schema’.

  2. El Conde says:

    Thank you, Arthur!
    A dependency on the EF in domain layer, does not look good.
    Regards

  3. gapipro says:

    Is it possible to create index with include?

    CREATE NONCLUSTERED INDEX [Test1]
    ON [dbo].[Logs] ([SampleId],[Date])
    INCLUDE ([Value])

  4. Daniel says:

    Hi,
    how can I use this with EntityTypeConfiguration ?

    this.Property(p => p.TenantId).HasColumnAnnotation(“Index”, new IndexAttribute(“IX_TenantId_Name”, 1));
    this.Property(p => p.Name).HasColumnAnnotation(“Index”, new IndexAttribute(“IX_TenantId_Name”, 2));

    With that I get following error:
    “An object of type ‘IndexAttribute’ cannot be serialized by the IndexAnnotationSerializer. Only ‘IndexAnnotation’ objects can be serialized.”

    I cannot use IndexAttribute as an annotation, because TenantId is declared on a base class.

    Regards,
    Daniel

  5. ChangedDaily says:

    Hi, I wonder if the problem I am having is in some way related to this feature… In the code below I am creating a context based on a Type and then checking to see if the database exists. If it does not exist it is created. This works – first time around when the database does not exist. However, when the database already exists I get the error:

    “The specified index already exists. [ IX_DonatedBy_PersonId ]”

    when the line ObjectContext… is executed.

    public void GetDestinationTableAndFieldNames(Type t)
    {
    /* Create a context instance to which data will be imported. */
    DbContext dBContext = (DbContext)Activator.CreateInstance(t, “Import.sdf”);

    if (!dBContext.Database.Exists())
    {
    dBContext.Database.Initialize(true);
    }
    /* We need the ObjectContext for getting the associations. */
    ObjectContext oBContext = ((IObjectContextAdapter)dBContext).ObjectContext;

    Note that I have provided a constructor in my DbContext that takes a string to set the ConnectionString.
    I also have several migrations applied to the database. I have set the initialisation strategy to “MigrateDatabaseToLatestVersion”.

    Can you offer any help on this?

    Thanks

    • It could be that you are hitting this issue: http://entityframework.codeplex.com/workitem/2076
      Can you try with the latest nightly build of 6.1 and see if it fixes the problem?

      • ChangedDaily says:

        Hi, thanks for the pointer (no pun intended). I installed the latest build but still had the issue. I then looked again at the link you provided to see if I had missed anything. This prompted me to run a migration. The migration was ‘empty’ (I had not changed the model just EF) but it has fixed the problem. I’ll bet this is in the instructions somewhere. :)

  6. Daniel says:

    The example provided with your link work for me. Thanks!!

    BTW: We have to use HasColumnAnnotation instead of HasAnnotation.

  7. Jeff says:

    Similar to what gapipro requested above, I’d like to see support for filtered indexes, so you can have unique nullable foreign keys:

    CREATE UNIQUE NONCLUSTERED INDEX [IX_ServiceID] ON [dbo].[Circuit]
    (
    [ServiceID] ASC
    )
    WHERE ([ServiceID] IS NOT NULL)

    Other than those little details – I think you guys have just about nailed it.

  8. erhan says:

    Is it possible to create unique index for nullable column, which is ignores nulls? (for sql server 2012)

  9. Since entities always need to have a primary key, and the primary key is always generated as clustered, the “IsClustered” parameter of the IndexAttribute seems pretty useless to me (unless I implement my own SQL generator subclass), or am I missing something?

    (See also, e.g., http://stackoverflow.com/questions/22813137/in-entity-frameowk-6-1-how-can-i-use-the-indexattribute-to-define-a-clustered-i.)

    • @Fabian In order to use it you have to manually edit your Migration to not create a clustered primary key. You should not need to create your own SQL generator class. That being said, in retrospect it was probably a bad idea to include IsClustered on the attribute itself.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s