Creating a custom migration operation in Entity Framework

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 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 the basic bricks are all in right place now 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!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s