EF Code First: CreateIndex - coverage index
Can I use the EF Code First Migrations CreateIndex syntax to create a Coverage Index (* see below what the Coverage Index is).
For example, I can create a simple index in manual migration like this:
CreateIndex("RelatedProduct", "RelatedId");
It has a final argument named "anonymous arguments" which indicates that it can handle whatever the underlying provider supports, but it's not clear how I define what that support is. Is this possible or do I need to resort to flat SQL?
* The coverage index is where the RDB stores duplicate data in leaf nodes, not just pointers to the main table. It is essentially a duplication of a table reordered by columns in an index containing only the columns most used in this type of search.
source to share
I believe the coverage index is a non-clustered index that spans the query (so it doesn't require additional lookups back to the table). What you are describing is an additional feature of such an index, which allows you to include data that is not part of the leaf-level index key.
CreateIndex
doesn't support it now. You must either use Sql
directly or you can check the EF source code and add support INCLUDE
to the call CreateIndex
, CreateIndexOperation
and the associated method Generate
in the SQL generator.
source to share
You cannot use a CreateIndex call to do this, but you can provide your own alternative on the side without changing the EF source. The core of it emits the original Sql in your Up () method for 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(")");
}
source to share