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

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.