EF Code First Migrations–Create Index with INCLUDE

The problem:

Using Entity Framework code first migrations to create a complex index that defines “INCLUDES” as part of the index. Yeah, good luck doing that using Entity Frameworks fluent notation using LINQ ModelBuilder. Btw, the example below is an ASP.Net Core 1.0 implementation.

The solution:

Plug into the Code First migrations pipeline by simply defining your own custom sql.

Create a “dummy” migration file and add the code below that is just using the model builder’s SQL method to run any custom sql. Again you should have an empty Up and Down method unless you made other mapping changes, which is OK, just add the SQL statement below. Of course you need to rename the index and fields you want to include in the index.

To create migrations file via command line, make sure you are in the project folder that contains the EF DbContext.

dotnet ef migrations add CreateIndexForInvoice –c <my context name>

It will create the empty migrations file, now add your custom index as in the example below. Note that if you already have an index with that name you will not be able to apply the migration successfully to the DB. A safer implementation would be to first check if the index already exists inside the SQL statement.

public partial class CreatIndexesForInvoice : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"
            CREATE NONCLUSTERED INDEX [IX_Invoice_DateTime]
            ON [dbo].[Invoice] ([DateTime])
            INCLUDE ([Id],[Number])
        ");

    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropIndex("IX_Invoice_DateTime");
    }
}

 

Print | posted @ Wednesday, November 16, 2016 12:09 PM

Comments on this entry:

No comments posted yet.

Post A Comment
Title:
Name:
Email:
Comment:
Verification: