How can Entity Framework Code First Create Index in Descendent order?

Question

In fluent API, we can specify an Index on a field:

var indexAttr = new IndexAttribute("IX_EmployeeNumber")
{
    IsClustered = true
};

Property(c => c.EmployeeNumber)
    .HasColumnAnnotation("Index", new IndexAnnotation(indexAttr ))
    .HasMaxLength(8)
    .IsRequired();

After Add-Migration, we will get this CreateIndex statement:

CreateIndex("dbo.Employees", "EmployeeNumber", clustered: true, name: "IX_EmployeeNumber");

After a Update-Database, we have a sql like this:

CREATE CLUSTERED INDEX [IX_EmployeeNumber] ON [dbo].[Employees]
(
    [EmployeeNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

My question is: how can I create this index in descendent order? Something like:

CREATE CLUSTERED INDEX [IX_EmployeeNumber] ON [dbo].[Employees]
(
    [EmployeeNumber] DESC

I couldn't find related parameters in CreateIndex. Is it possible to be achieved in Code First migration?

Of course we know we could get everything working with DbMigration.Sql(...some sql statement...). But, how can we obtain the desired result with CreateIndex()?

Answer 1

Take a look at how to customize code first migrations from the blog of Rowan Miller (one of the Entity Framework program managers).

This describes how to override the SqlServerMigrationsSqlGenerator sql generator to allow custom arguments to affect the generated sql.

As it happens, the scenario he walks through is extending the default behaviour of the CreateIndex migration operation to support specifying the creation of a descending index.

However, I don't think it is possible to expose this extension point to your code-first domain model creation code.

Answer 2

EF 7 Supports Index sort order!

With EF 7 you can have multiple sorting orders on multiple columns.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .HasIndex(b => new { b.Url, b.Rating })
        .IsDescending(false, true); //Added in EF 7.0
}

Answer 3

I wanted to do what you were asking but also do it with multiple columns and multiple orders.

Based on Matt Caton's answer and Rowan Miller blog, I was able to answer my own question and get a custom solution that works as generic as possible (knowing that you cannot extend Entity Framework classes easily to add properties that don't exist).

See this answer for the exact solution.