Creating a Custom Migration Operation in Entity Framework

• Download source code

Introduction

Since the release of Entity Framework 6.0, it became possible to create custom migration operations enabling us to make operations similar to the CreateTable and DropTable functions. This way, we can encapsulate more database definitions such as adding a Check constraint or a description to a column (or any other object).

Of course, we had means to do this before version 6.0 as well using the SQL function of the DbMigration which looked like this:

Sql("ALTER TABLE [Products] ADD CONSTRAINT [CK_Products_SKU] " +
    "CHECK (SKU LIKE '[A-Z][A-Z]-[0-9][0-9]%')");

Creating a migration operation is however a big improvement over using the SQL function in the migration step and executing the SQL statement that was passed in regardless of what database is used. Thus the code we get using custom migration operations not only looks better but it is database agnostic as well.

Now let’s see how this looks like in a concrete example where we create a check constraint on the Product entity’s SKU property by using a custom migration operation. To achieve this, first we will need a new MigrationOperation that contains all necessary information to create a check constraint such as table and column name, the actual content of the check constraint and optionally the constraint’s name:

public class CreateCheckConstraintOperation : MigrationOperation
{
    private string _table;
    private string _column;
    private string _checkConstraint;
    private string _checkConstraintName;

    public CreateCheckConstraintOperation()
        : base(null)
    {
    }

    public string Table
    {
        get { return _table; }
        set
        {
            if (string.IsNullOrWhiteSpace(value))
            {
                throw new ArgumentException(
                    "Argument is null or contains only white spaces.",
                    "value");
            }

            _table = value;
        }
    }

    public string Column
    {
        get { return _column; }
        set
        {
            if (string.IsNullOrWhiteSpace(value))
            {
                throw new ArgumentException(
                    "Argument is null or contains only white spaces.",
                    "value");
            }

            _column = value;
        }
    }

    public string CheckConstraint
    {
        get { return _checkConstraint; }
        set
        {
            if (string.IsNullOrWhiteSpace(value))
            {
                throw new ArgumentException(
                    "Argument is null or contains only white spaces.",
                    "value");
            }

            _checkConstraint = value;
        }
    }

    public string CheckConstraintName
    {
        get { return _checkConstraintName; }
        set
        {
            if (string.IsNullOrWhiteSpace(value))
            {
                throw new ArgumentException(
                    "Argument is null or contains only white spaces.",
                    "value");
            }

            _checkConstraintName = value;
        }
    }

    public override bool IsDestructiveChange
    {
        get { return false; }
    }

    public string BuildDefaultName()
    {
        return string.Format("CK_{0}_{1}", Table, Column);
    }
}

The second part is to create the code that will actually render the SQL script for creating a check constraint:

public class CheckConstraintMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
    protected override void Generate(MigrationOperation migrationOperation)
    {
        var checkConstraintOperation = migrationOperation as CreateCheckConstraintOperation;

        if (checkConstraintOperation != null)
        {
            if (checkConstraintOperation.CheckConstraintName == null)
            {
                checkConstraintOperation.CheckConstraintName = checkConstraintOperation.BuildDefaultName();
            }

            using (var writer = Writer())
            {
                writer.WriteLine(
                    "ALTER TABLE {0} ADD CONSTRAINT {1} CHECK ({2})",
                    Name(checkConstraintOperation.Table),
                    Quote(checkConstraintOperation.CheckConstraintName),
                    checkConstraintOperation.CheckConstraint
                );
                Statement(writer);
            }
        }
    }
}

In this code, we use the Writer function implemented in the SqlServerMigrationSqlGenerator to get an IndentedTextWriter which is basically a TextWriter, however it contains a very important addition: the capability to add indentation to the generated text. :)

What we want to create with this writer is a statement like this:

ALTER TABLE [Products] ADD CONSTRAINT [CK_Products_SKU]
      CHECK (SKU LIKE '[A-Z][A-Z]-[0-9][0-9]%')

To do this, we use the WriteLine function of the writer we got from base class. To do this, the right way, like formatting and such, we will use the functions Name and Quote, the latter adds quotes while the earlier generates a quoted name which can either contain the schema or not.

Now that the basic bricks are all in right place, we just have to connect the dots. First, we add our custom SQL generator to the DbMigrationsConfiguration:

internal sealed class Configuration : DbMigrationsConfiguration<ProductContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        SetSqlGenerator(
            "System.Data.SqlClient",
            new CheckConstraintMigrationSqlGenerator()
        );
    }
}

After that, we add shortcuts for creating a check constraint in a migration script just like we can use the CreateIndex to create an index.

public static class DbMigrationExtensions
{
    public static void CreateCheckConstraint(this DbMigration migration, string table, string column, string checkConstraint)
    {
        var createCheckConstraint = new CreateCheckConstraintOperation
        {
            Table = table,
            Column = column,
            CheckConstraint = checkConstraint
        };

        ((IDbMigration)migration).AddOperation(createCheckConstraint);
    }
}

And at last, we add a new migration via the Add-Migration command and we use the extension method CreateCheckConstraint to add a check constraint:

public partial class AddCheckConstraint : DbMigration
{
    public override void Up()
    {
        this.CreateCheckConstraint("Products", "SKU", "SKU LIKE '[A-Z][A-Z]-[0-9][0-9]%'");
    }

    public override void Down()
    {
    }
}

Now there is everything, just give the Update-Database command and you have a shiny new check constraint on the SKU column. And at last: you can download the source code from GitHub.

I hope you liked this post and I’m looking forward to your feedback!

CodeProject