Entity Framework 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
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>();
}