How to add an index on multiple columns with ASC/DESC sort using the Fluent API?

Question

I have a MVC ASP.NET application using Entity Framework 6 - Code First approach.

Using the Fluent API, how can I add an index on multiple columns with ASC/DESC sort that is different for each column ?

I've seen many examples using multiple columns but no way to set the sort order of the columns in the index.

Table
-----
Id
Type
DateFor
DateCreated
Value

I want an index on the following columns: Type(ASC), DateFor(Desc), DateCreated(Desc).

Answer 1

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
}

So in your case would be this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<YourModel>()
        .HasIndex(b => new { b.Type, b.DateFor, b.DateCreated })
        .IsDescending(false, true, true); 
}

Answer 2

Short answer: Entity Framework 6 does not allow multiple indexes with different sorts.

Long answer: It may not be possible to do it directly but it can be achieved with some tweaking. After a lot of reading, I found that it would be really complicated to create a new class that would inherit IndexAnnotation and add a SortOrder property.

The easiest way I found to achieve this was to see what existing property I could tweak to achieve the multiple index sort. Using the Name property could do it as it's a string. You can add the sort index directly in the name and intercept it later when generating the SQL code.

So let's assume I need to index the properties like this:

• Type (ASC)
• DateFor (Desc)
• DateCreated(Desc)

I would then name my index followed by a separator (:) and the sort orders. It would look like this:

var indexName = "IX_Table:ASC,DESC,DESC";

The index with multiple fields would look like this:

this.Property(t => t.Type)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName,
        new IndexAnnotation(new[]
            {
                new IndexAttribute(indexName) { Order = 1 }
            }
        )
    );

this.Property(t => t.DateFor)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName,
        new IndexAnnotation(new[]
            {
                new IndexAttribute(indexName) { Order = 2 }
            }
        )
    );

this.Property(t => t.DateCreated)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName,
        new IndexAnnotation(new[]
            {
                new IndexAttribute(indexName) { Order = 3 }
            }
        )
    );

We must now create a custom SQL generate class in order to generate the right SQL code to parse our "tweaked" index name:

public class CustomSqlServerMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
    protected override void Generate(CreateIndexOperation createIndexOperation)
    {
        using (var writer = Writer())
        {
            writer.Write("CREATE ");

            if (createIndexOperation.IsUnique)
            {
                writer.Write("UNIQUE ");
            }

            if (createIndexOperation.IsClustered)
            {
                writer.Write("CLUSTERED ");
            }
            else
            {
                writer.Write("NONCLUSTERED ");
            }

            string name = createIndexOperation.Name;
            string[] sorts = {};
            if (createIndexOperation.Name.Contains(":"))
            {
                var parts = createIndexOperation.Name.Split(':');

                if (parts.Length >= 1)
                {
                    name = parts[0];
                }
                if (parts.Length >= 2)
                {
                    sorts = parts[1].Split(',');
                }
            }

            writer.Write("INDEX ");
            writer.Write(Quote(name));
            writer.Write(" ON ");
            writer.Write(Name(createIndexOperation.Table));
            writer.Write("(");

            // Add the columns to the index with their respective sort order
            string fields = "";
            if (sorts.Length == 0 || sorts.Length == createIndexOperation.Columns.Count)
            {
                for (int i=0 ; i<createIndexOperation.Columns.Count ; i++)
                {
                    string sort = "ASC";
                    if (sorts.Length == 0)
                    {
                        // Do nothing
                    }
                    else if (sorts[i] != "ASC" && sorts[i] != "DESC")
                    {
                        throw new Exception(string.Format("Expected sort for {0} is 'ASC' or 'DESC. Received: {1}", name, sorts[i]));
                    }
                    else 
                    { 
                        sort = sorts[i];  
                    }

                    fields = fields + Quote(createIndexOperation.Columns[i]) + " " + sort + ",";
                }
                fields = fields.Substring(0, fields.Length - 1);
            }
            else
            {
                throw new Exception(string.Format("The sort (ASC/DEC) count is not equal to the number of fields in your Index ({0}).", name));
            }

            writer.Write(fields);

            writer.Write(")");
            Statement(writer);
        }
    }
}

Finally, you need to tell Entity Framework to use your new code generated method instead of the default one by editing your Configuration.cs file:

internal sealed class MyConfiguration : DbMigrationsConfiguration<MyContext>
{

    /// <summary>
    /// Constructor
    /// </summary>
    public MyConfiguration()
    {
        // Other stuff here...

        // Index/Unique custom generation (Ascending and Descending)
        SetSqlGenerator("System.Data.SqlClient", new CustomSqlServerMigrationSqlGenerator());
    }
}

That's it. It may not be the cleanest solution but if you generate your entities on the fly (as I do), you will save a lot of time and avoid forgetting to run your raw SQL.

See the code here

A big thank you to Rowan Miller and all the articles on his blog. This answer was inspired by: Customizing Code First Migrations Provider.

Answer 3

You can make it manually editing Migrations like this :

public override void Up()
{
    Sql("CREATE NONCLUSTERED INDEX [IX_Index_name] ON [dbo].[TableName] ([ColumnName1] Asc,[ColumnName2] Desc,[ColumnName3] Desc)");
}

public override void Down()
{
     Sql("DROP INDEX [dbo].[TableName].[IX_Index_name]");
}

Answer 4

I really like @Maxime's answer, but it's quite complex, but I'll try to learn that stuff.

My solution is bit easier, and it just work, so adding it here, in case it would be useful for someone.

I've managed to do this by manually editing Migrations, and adding proper code. On model check EF only checks if index exists and not it's order.

    public override void Up()
    {
        DropIndex("dbo.MonitoringItemHistory", "IX_ItemDate");
        Sql("ALTER TABLE dbo.MonitoringItemHistory DROP CONSTRAINT [PK_dbo.MonitoringItemHistory]");
        CreateIndex("dbo.MonitoringItemHistory", new[] { "MonitoringItemId", "ChangeTime" }, clustered: true, name: "IX_ItemDate");
        Sql("ALTER TABLE dbo.MonitoringItemHistory ADD CONSTRAINT [PK_dbo.MonitoringItemHistory] PRIMARY KEY NONCLUSTERED (Id)");
    }

    public override void Down()
    {
        Sql("ALTER TABLE dbo.MonitoringItemHistory DROP CONSTRAINT [PK_dbo.MonitoringItemHistory]");
        DropIndex("dbo.MonitoringItemHistory", "IX_ItemDate");
        Sql("ALTER TABLE dbo.MonitoringItemHistory ADD CONSTRAINT [PK_dbo.MonitoringItemHistory] PRIMARY KEY CLUSTERED (Id)");
        CreateIndex("dbo.MonitoringItemHistory", new[] { "MonitoringItemId", "ChangeTime" }, name: "IX_ItemDate");
    }

and in my DB Entity code:

    [Index("IX_ItemDate", 1, IsClustered = true)]
    public int MonitoringItemId { get; set;}
    [Index("IX_ItemDate", 2, IsClustered = true)]
    public DateTimeOffset ChangeTime { get; set; }