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. stackoverflow.com/questions/25293161/… — Kim Ki Won
– Kim Ki Won, Commented Oct 13, 2015 at 2: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
– Michael Sagalovich, Commented Oct 14, 2015 at 7:13

alastairtree · Accepted Answer · 2016-01-04 16:49:54Z

7

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.

edited Jan 4, 2016 at 16:49

answered Jan 19, 2014 at 15:02

alastairtree

4,3581 gold badge35 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating indexes with Entity Framework 6 Migrations

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related