Indexes in Code-Based EF Migrations

In the Adding Indexes with EF Migrations post I showed how to add indexes to a database created by EF Code First with a separate migration step. When using code-based migrations to add a table, there is another, better way to add indexes, if it is done together with the table. If you’ve read my Type Safe SelectList Factory post you know my feelings about code that contains class or property names as strings. I was considering writing a similar post about indexes in EF Migrations until I found out that there is a built in index creation, using lambdas.

First I’ll scaffold up an ordinary code-based migration.

PM> Add-Migration People
Scaffolding migration 'People'.
The Designer Code for this migration file includes a snapshot of 
your current Code First model. This snapshot is used to calculate 
the changes to your model when you scaffold the next migration. If 
you make additional changes to your model that you want to include 
in this migration, then you can re-scaffold it by running 
'Add-Migration 201202232002181_People' again.

The file added contains the table creation code.

namespace TestLib.Migrations
{
  using System.Data.Entity.Migrations;
 
  public partial class People : DbMigration
  {
    public override void Up()
    {
      CreateTable(
        "People",
        c => new
          {
            PersonId = c.Int(nullable: false, identity: true),
            BirthYear = c.Int(nullable: false),
          })
        .PrimaryKey(t => t.PersonId);
 
    }
 
    public override void Down()
    {
      DropTable("People");
    }
  }
}

To add an index, just add a call to Index() after the call to PrimaryKey().

public override void Up()
{
  CreateTable(
    "People",
    c => new
      {
        PersonId = c.Int(nullable: false, identity: true),
        BirthYear = c.Int(nullable: false),
      })
    .PrimaryKey(t => t.PersonId)
    .Index(p => p.BirthYear);
 
}

To add an index containing multiple columns, create an anonymous type containing those columns.

    .Index(p => new { p.BirthYear, p.PersonId });

When adding the indexes together with the table, this is a better way than a separate call to CreateIndex(). The fluent syntax method to create an index is completely type safe which is always good. I still can’t understand why functions like DbMigration.CreateIndex() only accepts strings and not lambdas to select columns.

This post is part of the EF Migrations series.<< Updating a Table with EF MigrationsEF Migrations and a Merge Conflict >>

6 comments

    1. You don’t have to do anything separate for the index. It is enough to drop the table with DropTable(...). When the table is removed, any associated indexes are automatically removed too.

      1. Thx for your reply. In my case I need the posibillity to switch between releases, therefor I need a proper Rollback (Down) method :-)

      2. If you want to drop just the index, without dropping the table, you can use the DropIndex method.

        Before you do that, you should keep in mind that EF Migrations is designed to have the Up and Down methods being exact opposites of each other. If you create the table and the index together in the Up method, both should be removed in the Down method.

      3. Okay, that’s a clear answer. Then I have to use the non-typesafe methods Create- and DropIndex. That’s a pitty… Thanks again for your replies.

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.