[.NETWorld] 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:

1
2
3
4
5
6
7
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:

1
2
3
4
5
6
7
8
9
10
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:

1
2
3
4
5
6
7
8
9
10
11
12
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:

1
2
3
4
5
6
7
8
9
10
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:

1
2
3
4
5
6
7
8
9
10
11
12
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
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:

1
2
3
4
5
6
7
8
9
10
11
12
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:

1
2
3
4
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.

Advertisements

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