Creating indexes with Entity Framework 6 Migrations

Question

Is there a way to create an index in MS SQL Server database using Entity Framework Code First Migrations, if the index has to be:

• descending by at least one column
• including other columns

?

It has to produce something like this:

CREATE NONCLUSTERED INDEX [IX_IndexName] ON [dbo].[TableName]
(
    [Column1] ASC,
    [Column2] DESC
)
INCLUDE ([Column3], [Column4])

I found an article on this very topic, but it offers quite a cumbersome solution. Possibly, something has improved since the time the article was written.

I am also aware of an ability to make my migration execute arbitrary SQL code, but I really want to be able to use some version of CreateIndex method which does all the dirty work for me instead of writing SQL code myself.

Answer 1

I just came across the same problem and it seems it is not part of the migrations API at the moment. My workaround was just to execute the sql for the up migration and use the DropIndex method for the down migration.

public override void Up()
{
    Sql("CREATE NONCLUSTERED INDEX IX_IndexName ON TableName ([Column1], [Column2] DESC) INCLUDE ([Column3], [Column4])");
}

public override void Down()
{
    DropIndex("TableName", "IX_IndexName");
}

It is not pretty, could be tidied up into a reusable method/extension etc, but it does the job.