Migrations provides a set of strongly typed APIs for performing common operation, for example CreateIndex(“dbo.Blogs”, “Url”). We also provide a method that allows you to run arbitrary SQL when you want to do something that isn’t supported by the API, for example Sql(“GRANT SELECT ON dbo.Blogs to guest”).

There are some disadvantages to the Sql method though… firstly that you have to write SQL and secondly your migration is no longer database provider independent.

Thanks to a recent pull request that we accepted from iceclow you can now add your own strongly typed APIs for operations that you find yourself needing.

Creating Our Own Operation

In our example we’re going to add an operation that allows us to grant permissions to a user for a table. Ultimately we want to write something like GrantPermissions(“dbo.Blogs”, “guest”, Permissions.Select).

The first step is to create a custom operation that derives from MigrationOperation. There really aren’t any requirements on the shape of this class other than implementing the IsDestructiveChange property.

using System.Data.Entity.Migrations.Model;

namespace ExtendingMigrations.Migrations
{
  public enum Permission
  {
    Select,
    Update,
    Delete
  }

  public class GrantPermissionOperation : MigrationOperation
  {
    public GrantPermissionOperation(string table, string user, Permission permission)
      : base(null)
    {
      Table = table;
      User = user;
      Permission = permission;
    }

    public string Table { get; private set; }
    public string User { get; private set; }
    public Permission Permission { get; private set; }

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

Next we can write an extension method that provides a simple way to add one of these operations within a migration. You’ll notice we’re using the IDbMigration interface. This interface gives us access to APIs on DbMigration that are generally hidden when writing migrations. One such method is the ability to add a MigrationOperation – we hide this one so that folks use the nice APIs that we provide, much like the one we are writing here.

using System.Data.Entity.Migrations;
using System.Data.Entity.Migrations.Infrastructure;

namespace ExtendingMigrations.Migrations
{
  public static class Extensions
  {
    public static void GrantPermission(this DbMigration migration, string table, string user, Permission permission)
    {
      ((IDbMigration)migration)
        .AddOperation(new GrantPermissionOperation(table, user, permission));
    }
  }
}

Of course, you could just write the code from the extension method in your migration… but now we can write the following code which looks nicer and is consistent with the rest of the migrations API.

namespace ExtendingMigrations.Migrations
{
    using System;
    using System.Data.Entity.Migrations;

    public partial class GrantGuestPermissions : DbMigration
    {
        public override void Up()
        {
          this.GrantPermission("dbo.Blogs", "guest", Permission.Select);
        }

        public override void Down()
        {
        }
    }
}

Generating SQL for our Operation

If we were to try and apply our new migration we’d get an exception because the default SQL generator doesn’t know how to process our new operation. Fortunately we can just derive from the existing migrator and add logic to generate SQL for our new operation.

Notice that we aren’t calling base.Generate if the operation isn’t one of our custom operations – the Generate(MigrationOperation) method only gets called for operations the default SQL generator doesn’t know how to process.

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

namespace ExtendingMigrations.Migrations
{
  public class MySqlServerMigrationSqlGenerator : SqlServerMigrationSqlGenerator
  {
    protected override void Generate(MigrationOperation migrationOperation)
    {
      var operation = migrationOperation as GrantPermissionOperation;
      if (operation != null)
      {
        using (var writer = Writer())
        {
          writer.WriteLine(
            "GRANT {0} ON {1} TO {2}",
            operation.Permission.ToString().ToUpper(),
            operation.Table,
            operation.User);

          Statement(writer);
        }
      }
    }
  }
}

And finally we register our new SQL generator in the migrations configuration class.

namespace ExtendingMigrations.Migrations
{
  using System.Data.Entity.Migrations;

  internal sealed class Configuration : DbMigrationsConfiguration<ExtendingMigrations.BloggingContext>
  {
    public Configuration()
    {
      AutomaticMigrationsEnabled = false;

      // Register our custom generator
      SetSqlGenerator("System.Data.SqlClient", new MySqlServerMigrationSqlGenerator());
    }
  }
}

Testing it Out

If we run Update-Database -Script in package manager console, migrations will give us a script of the changes it would apply to the database. We can now see migrations including SQL for our custom operation.

GRANT SELECT ON dbo.Blogs TO guest

INSERT [dbo].[__MigrationHistory]([MigrationId], [ContextKey], [Model], [ProductVersion])
VALUES ('201302272012532_GrantGuestPermissions', 'ExtendingMigrations.Migrations.Configuration', 0x1F8B0800000..., '6.0.0-alpha3-20222')

Source Code

As promised… you can get the complete Visual Studio 2012 solution here…