Code First Migrations is included as part of Entity Framework starting with the EF 4.3 release.

One feature we haven’t really blogged about yet is the ability to include additional arguments in a migration operation. These arguments are then made available to the SQL generator. You can then derive from the SQL generators that we ship, and customize the generated SQL based on the arguments you specify.

I’m going to assume you have a working knowledge of Code First Migrations… if you don’t, take a minute to read the Code-Based Migrations Walkthrough.

 

The Scenario

Code First Migrations allows you to add additional indexes to your database from within a code-based migration. These indexes are always ascending though… and you may find yourself wanting to add descending indexes. In this post we’ll look at extending the existing SQL Server generator to handle this scenario.

 

Specifying Additional Arguments

All the migration operation APIs, that are exposed in a code-based migration, include an optional anonymousArguments parameter. This parameter allows you to specify some additional arguments that should be stored in a property bag on the operation.

The additional arguments are specified using anonymous type syntax. The values are converted into a Dictionary<string, object> that is made available during SQL generation.

To specify the sort order of an index we are going to use the SortOrder key. When we create an index we’ll assign either “Ascending” or “Descending”. We’ll write the SQL generator so that it assumes an ascending index if the SortOrder isn’t specified.

Here is an example of the code we could write in a code-based migration:

CreateIndex(table: "Blogs", column: "Title", anonymousArguments: new { SortOrder = "Descending" });

 

Customizing the SQL Generator

Now it’s time to customize the SQL generator for SQL Server, so that it reacts to our custom argument. We can create our own generator that derives from the existing SqlServerMigrationsSqlGenerator. The generator is structured so that it’s easy for you to override just the operations you want to customize the SQL for. In our case we just want to change the SQL that is generated for a CreateIndexOperation .

using System.Data.Entity.Migrations.Model;
using System.Data.Entity.Migrations.Sql;
using System.Linq;

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

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

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

                // Calculate sort order
                object sortOrder;
                createIndexOperation.AnonymousArguments.TryGetValue("SortOrder", out sortOrder);
                var sortOrderSql = sortOrder != null && sortOrder.ToString() == "Descending"
                    ? "DESC"
                    : "ASC";

                // Specify columns, including sort order
                writer.Write(string.Join(",", createIndexOperation.Columns.Select(c => Quote(c) + " " + sortOrderSql)));

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

The code inside the overridden Generate method if basically a copy/paste of the code in the base provider. The only customization is to the code that generates the columns to be included in the index. The customized code reads the anonymous arguments on the CreateIndexOperation and calculates the SQL operator to specify the sort order. This operator is then added to each column in the generated SQL.

Here is an example of the SQL that will be generated:

CREATE INDEX [IX_Title] ON [Blogs]([Title] DESC)

NOTE: You may notice that the code makes use of the Writer method to get a text writer to use for building the SQL. This isn’t mandatory, you can use any approach you want to create the SQL. The SQL you generate must be registered with the Statement method to ensure it gets executed.

 

Registering the Customized Provider

Now that you have a custom provider it’s time to let Migrations know to use your new provider, instead of the default, when running against SQL Server. You do this in the constructor of your migrations configuration class, using the SetSqlGenerator method. SQL generators are registered based on the provider invariant name of the database provider they generate SQL for, for SQL Server this is “System.Data.SqlClient”.

public Configuration()
{
    AutomaticMigrationsEnabled = false;

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

 

Summary

In this post you saw how to add additional arguments to a migration operation using the anonymousArguments parameter. You then saw how to customize the existing SQL Server generator to generate different SQL based on these arguments. Finally you saw how to register your custom generator so that Code First Migrations will use it in place of the default SQL Server generator.