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.

I found duplicated article. http://stackoverflow.com/questions/25293161/how-can-entity-framework-code-first-create-index-in-descendent-order — Kim Ki Won, Oct 13 '15 at 02:40
@KimKiWon, thank you! while my post is older, weirdly that one received more answers :) they look useful indeed, so anyone stopping by please follow the link above. One issue though is that my question is also about INCLUDE, but I think the solutions offered there could be expanded to support INCLUDE as well. — Michael Sagalovich, Oct 14 '15 at 07:13

alastairtree · Answer 1 · 2016-01-04T16:49:54.993

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.

Creating indexes with Entity Framework 6 Migrations

1 Answers1

Linked