EF6: Writing Your Own Code First Migration Operations

Posted on February 27, 2013. Filed under: Entity Framework, Visual Studio | Tags: , , , , , |

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…

About these ads

Make a Comment

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

21 Responses to “EF6: Writing Your Own Code First Migration Operations”

RSS Feed for RoMiller.com Comments RSS Feed

[…] to create custom migrations operations and process them in a custom migrations SQL generator. This blog post provides an example of using this new […]

[…] to create custom migrations operations and process them in a custom migrations SQL generator. This blog post provides an example of using this new […]

[…] Miller wrote about writing your own Code First Migrations operations, configuring unmapped base types using Code First custom conventions, and mapping all private […]

Hi Rowan,
I am using a Function import which calls a SQL Server procedure. It works well when the stored proc returns results but when there are no records returned, it throws an exception which looks like this

“The data reader is incompatible with the specified ‘ResultType1′. A member of the type, ‘EmpoyeeID’, does not have a corresponding column in the data reader with the same name.”

Please advise.

Thanks !!

that’s interesting, but I don’t like very much the choosen strategy to translate in sql. If I understood well,I can create a library of custom migration operations, but I have to create a single SqlGenerator to make them work, and update it every time I add a new operation.

It should be reasonably easy to write an extensible one yourself. The lack of explicit support is hardly a limitation.

[…] going to be using the techniques shown in my past post on Writing Your Own Migrations Operations. I’m not going to rehash what I already covered there. If you want more details about the […]

[…] Migrations Operations were enabled by a contribution from iceclow and this blog post provides an example of using this new […]

[…] Custom Migrations Operations were enabled by a contribution from iceclow and this blog post provides an example of using this new […]

[…] Migrations Operations were enabled by a contribution from iceclow and this blog post provides an example of using this new […]

[…] Migrations Operations were enabled by a contribution from iceclow and this blog post provides an example of using this new […]

[…] Migrations Operations were enabled by a contribution from iceclow and this blog post provides an example of using this new […]

[…] Migrations Operations were enabled by a contribution from iceclow and this blog post provides an example of using this new […]

[…] Custom Migrations Operations enabled you to add additional operations to be used in your code-based migrations. […]

[…] Custom Migrations Operations (Code First only)      code-based migrations に追加のオペレーションを作成する。 […]

[…] Custom Migrations Operations (Code First only)Create additional operations to be used in your code-based migrations . […]

[…] Custom Migrations Operations (Code First only) Create additional operations to be used in your code-based migrations . […]

Hi Rowan, is there a book or video course on working directly with the Code First API to managed migrations and the rest? Thanks

Nothing that I am aware of specifically on Migrations. We have some articles etc. on http://msdn.com/data/ef but it’s not structured into a course.


Where's The Comment Form?

    About

    Rowan works as a Program Manager for the ADO.NET Entity Framework team at Microsoft. He speaks at technical conferences and blogs at romiller.com. Rowan lives in Seattle, Washington with his wife Athalie. Prior to moving to the US he resided in the small state of Tasmania in Australia. Outside of technology Rowan's passions include snowboarding, mountain biking, horse riding, rock climbing and pretty much anything else that involves being active. The primary focus of his life, however, is to follow Jesus.

    RSS

    Subscribe Via RSS

    • Subscribe with Bloglines
    • Add your feed to Newsburst from CNET News.com
    • Subscribe in Google Reader
    • Add to My Yahoo!
    • Subscribe in NewsGator Online
    • The latest comments to all posts in RSS

    Meta

Liked it here?
Why not try sites on the blogroll...

Follow

Get every new post delivered to your Inbox.

Join 168 other followers

%d bloggers like this: