EF Code First: CreateIndex - Covering Index

Question

Is it possible to use the CreateIndex syntax of EF Code First Migrations to create a Covering Index (*see below for what a Covering Index is).

For example I can create a simple index in a manual migration like so:

CreateIndex("RelatedProduct", "RelatedId");

It has a final argument named "anonymous arguments" that specifies it can handle whatever the underlying provider supports - but it's not clear how I determine what that support would be. Is this possible or do I need to resort to flat SQL?

*A Covering Index is one where the RDB stores duplicated data in the leaf nodes, not just pointers to the main table. It's essentially a duplication of a table reordered by the columns in the index, containing just the columns most used in that type of search.

score 7 · Accepted Answer · answered Feb 14 '13 at 19:38

7

I think the covering index is non-clustered index which covers the query (so it doesn't require any additional lookups back to the table). What you describe is additional feature of such index which allows you including data which are not part of the index key on the leaf level.

CreateIndex doesn't support that now. You must either use Sql directly or you can check EF's source code and add support for INCLUDE into CreateIndex call, CreateIndexOperation and related Generate method in SQL Generator.

answered Feb 14 '13 at 19:38

Ladislav Mrnka

360,892
59
660
670

Yeah, the difference between a plain ol' (non-clustered) index and a Covering index is an index normally stores no data, just pointers to the table. A Covering Index additionally stores some copies of columns. http://stackoverflow.com/questions/609343/what-are-covering-indexes-and-covered-queries-in-sql-server Good call on inspecting source, I'll take a look. – Chris Moschini Feb 15 '13 at 01:19
You're correct; the anonymousArguments arg gave me hope, but the source simply stores these in a Dictionary and does nothing with them - future feature presumably. It does appear reasonably possible to fork the source however and add this capability. System.Data.Entity.Migrations.Sql.SqlServerMigrationSqlGenerator has a method protected virtual void Generate(CreateIndexOperation createIndexOperation) that appears straight-forward enough to add this to. – Chris Moschini Feb 15 '13 at 01:37

score 1 · Answer 2 · answered Dec 11 '15 at 02:03

You can't use the CreateIndex call to do this, but you can provide your own alternative from the side without modifying the source of EF. The core of it is emitting raw Sql in your Up() method of a manual migration, like:

// Build a string like
//@"create nonclustered index IX_IsPublished_OrderIndex
//on Project (IsPublished desc, OrderIndex asc)
//include [Key]"
var sb = new StringBuilder();
sb.Append("create nonclustered index [")
.Append(Name)
.Append("] on [")
.Append(Table)
.Append("] (")
.Append(String.Join(", ", Columns
    .Select(col => "[" + col.Name + "] " + (col.IsAsc ? "asc" : "desc"))
))
.Append(")");

if (Include != null && Include.Length > 0)
{
    sb.Append(" include (")
    .Append(String.Join(", ", Include.Select(c => "[" + c + "]")))
    .Append(")");
}

EF Code First: CreateIndex - Covering Index

2 Answers2