how to create index order desc through Dataannotations

Question

I need to create an index in my model, but I need it to be decreasing

Like I’m doing:

[Index("Ix_PrecoConsultData", IsUnique = false, Order = 1)]
public DateTime DataCadastro { get; set; }

It creates the index but in ASC order I need this index to be DESC Apparently this Order is for if you have more than one column in the index.

Answer 1

By default, there is nothing to do what you want. However, nothing prevents you from doing your Custom Migrations Generator, as shown in this answer.

To do this, simply create a class inheriting from Sqlservermigrationsqlgenerator and perform the configuration you want (not only for index).

To our example, we will do the following:

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);
        }
    }
}

Done this, just add the reference to your Configuration.cs, in this way:

public Configuration()
    {
        AutomaticMigrationsEnabled = false;

        SetSqlGenerator("System.Data.SqlClient", new CustomSqlServerMigrationSqlGenerator());

    }

Now, just decorate your attribute with the ending :DESC or :ASC, as desired. It would look something like this:

[Index("Ix_PrecoConsultData:DESC", IsUnique = false, Order = 1)]
public DateTime DataCadastro { get; set; }

And this will generate the following SQL:

CREATE NONCLUSTERED INDEX [IX_DATA] ON [dbo].[TABLENAME]([DataCadastro] DESC)

If you want more explanations, this article has the same implementation, but differently, using anonymousArguments{} in Migrations.

If you don’t like this solution, you can also create SQL at hand and add to the generated Migration in this way:

namespace Wmb.Mmn.Common.Migrations
{
    using System;
    using System.Data.Entity.Migrations;

    public partial class teste : DbMigration
    {
        public override void Up()
        {
            //Migration Gerada
            //CreateIndex("dbo.Categorias", "DataCriacao", name: "IX_DATA");

            //SQL Index
            Sql("CREATE NONCLUSTERED INDEX [IX_DATA] ON [dbo].[Categorias]([DataCriacao] DESC)");
        }

        public override void Down()
        {
            DropIndex("dbo.Categorias", "IX_DATA");
        }
    }
}