EF6: Switching Identity On/Off with a Custom Migration Operation

Posted on April 30, 2013. Filed under: Entity Framework, Visual Studio | Tags: , , , , , |

Here is the scenario, I have a class in my Code First model that has a primary key configured as an identity column. I want to change it so that I can generate values in my code. Alternatively I may want to change an existing column – that my code is generating values for – to have values generated by the database.

Migrations can’t handle this operation yet (although we plan to add support in the future). We don’t support it yet because it’s complicated… in SQL Server you can’t just make the change with an ALTER statement, some Jedi SQL skills are required.

I’m 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 components I’m going to write in this post… go read that post Smile.

Downloadable source code is included at the bottom of the post.
 


The Process

The steps for changing the identity setting on a column in SQL Server are:

  1. Drop all foreign key constraints that point to the primary key we are changing
  2. Drop the primary key constraint
  3. Rename the existing column (so that we can re-create the foreign key relationships later)
  4. Add the new primary key column with the new identity setting
  5. Update existing data so that previous foreign key relationships remain
    • If the new column is an identity column, we need to update all foreign key columns with the new values
    • If the new column doesn’t have identity on, we can copy the old values from the previous identity column
  6. Drop old primary key column
  7. Add primary key constraint
  8. Add back foreign key constraints

 


Component 1/3: Custom Migrations Operation

Our first task is a custom migrations operation that holds all the info we need to be able to generate SQL in our provider. In our case that is:

  • Whether we are switching identity on or off
  • What table and column we are changing the identity setting for
  • Any foreign key columns that reference this column

If you want a more detailed explanation of custom migrations operations then see my previous post on writing custom migration operations.

using System.Collections.Generic;
using System.Data.Entity.Migrations.Model;

namespace IdentityDemo.Migrations
{
    public class ChangeIdentityOperation : MigrationOperation
    {
        public ChangeIdentityOperation()
            : base(null)
        { }

        public IdentityChange Change { get; set; }
        public string PrincipalTable { get; set; }
        public string PrincipalColumn { get; set; }
        public List<DependentColumn> DependentColumns { get; set; }

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

    public enum IdentityChange
    {
        SwitchIdentityOn,
        SwitchIdentityOff
    }

    public class DependentColumn
    {
        public string DependentTable { get; set; }
        public string ForeignKeyColumn { get; set; }
    }
}

 


Component 2/3: Migration Extensions Methods

The next step is to provide some nice extension methods for creating the migration operation within a DbMigration. I’ve built a little object model that allows us to use a fluent style API to specify any dependent columns. In the last section you’ll see how this looks when used in a migration.

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

namespace IdentityDemo.Migrations
{
    public static class MigrationExtensions
    {
        public static IdentityChangeOperationWrapper ChangeIdentity(
            this DbMigration migration,
            IdentityChange change,
            string principalTable, 
            string principalColumn)
        {
            var operation = new ChangeIdentityOperation
            {
                Change = change,
                PrincipalTable = principalTable,
                PrincipalColumn = principalColumn,
                DependentColumns = new List<DependentColumn>()
            };

            ((IDbMigration)migration).AddOperation(operation);

            return new IdentityChangeOperationWrapper(operation);
        }

        public class IdentityChangeOperationWrapper
        {
            private ChangeIdentityOperation _operation;

            public IdentityChangeOperationWrapper(ChangeIdentityOperation operation)
            {
                _operation = operation;
            }

            public IdentityChangeOperationWrapper WithDependentColumn(
                string table, 
                string foreignKeyColumn)
            {
                _operation.DependentColumns.Add(new DependentColumn
                {
                    DependentTable = table,
                    ForeignKeyColumn = foreignKeyColumn
                });

                return this;
            }
        }
    }
}

 


Component 3/3: Custom SQL Generator

Finally, we need to write a SQL Generator that knows how to handle this operation for our database provider. I’m using SQL Server, so I’ve derived from SqlServerMigrationSqlGenerator and added logic to handle our custom operation.

The comments in the code show how each block of code relates to the steps in the ‘Process’ section at the start of the post.

Fortunately, there are existing migration operations that handle every step (except for the data migration in Step 5), so we’re making use of those.

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

namespace IdentityDemo.Migrations
{
    class MySqlServerMigrationSqlGenerator : SqlServerMigrationSqlGenerator
    {
        protected override void Generate(MigrationOperation migrationOperation)
        {
            var operation = migrationOperation as ChangeIdentityOperation;
            if (operation != null)
            {
                var tempPrincipalColumnName = "old_" + operation.PrincipalColumn;

                // 1. Drop all foreign key constraints that point to the primary key we are changing
                foreach (var item in operation.DependentColumns)
                {
                    Generate(new DropForeignKeyOperation
                    {
                        DependentTable = item.DependentTable,
                        PrincipalTable = operation.PrincipalTable,
                        DependentColumns = { item.ForeignKeyColumn }
                    });
                }

                // 2. Drop the primary key constraint
                Generate(new DropPrimaryKeyOperation { Table = operation.PrincipalTable });

                // 3. Rename the existing column (so that we can re-create the foreign key relationships later)
                Generate(new RenameColumnOperation(
                    operation.PrincipalTable, 
                    operation.PrincipalColumn, 
                    tempPrincipalColumnName));

                // 4. Add the new primary key column with the new identity setting
                Generate(new AddColumnOperation(
                    operation.PrincipalTable, 
                    new ColumnBuilder().Int(
                        name: operation.PrincipalColumn, 
                        nullable: false, 
                        identity: operation.Change == IdentityChange.SwitchIdentityOn)));

                // 5. Update existing data so that previous foreign key relationships remain
                if (operation.Change == IdentityChange.SwitchIdentityOn)
                {
                    // If the new column is an identity column we need to update all 
                    // foreign key columns with the new values
                    foreach (var item in operation.DependentColumns)
                    {
                        Generate(new SqlOperation(
                            "UPDATE " + item.DependentTable +
                            " SET " + item.ForeignKeyColumn +
                                " = (SELECT TOP 1 " + operation.PrincipalColumn + 
                                " FROM " + operation.PrincipalTable + 
                                " WHERE " + tempPrincipalColumnName + " = " + item.DependentTable + "." + item.ForeignKeyColumn + ")"));
                    }
                }
                else
                {
                    // If the new column doesn’t have identity on then we can copy the old 
                    // values from the previous identity column
                    Generate(new SqlOperation(
                        "UPDATE " + operation.PrincipalTable + 
                        " SET " + operation.PrincipalColumn + " = " + tempPrincipalColumnName + ";"));
                }

                // 6. Drop old primary key column
                Generate(new DropColumnOperation(
                    operation.PrincipalTable, 
                    tempPrincipalColumnName));

                // 7. Add primary key constraint
                Generate(new AddPrimaryKeyOperation
                {
                    Table = operation.PrincipalTable,
                    Columns = { operation.PrincipalColumn }
                });

                // 8. Add back foreign key constraints
                foreach (var item in operation.DependentColumns)
                {
                    Generate(new AddForeignKeyOperation
                    {
                        DependentTable = item.DependentTable,
                        DependentColumns = { item.ForeignKeyColumn },
                        PrincipalTable = operation.PrincipalTable,
                        PrincipalColumns = { operation.PrincipalColumn }
                    });
                }
            }
        }
    }
}

We need to register our new SQL Generator in our migrations configuration class.

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

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

 


Testing It Out

I have an existing Code First model that has a Blog class with an identity primary key. I already have migrations to make the database match the current model.

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Name { get; set; }

    public virtual List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogId { get; set; }
    public virtual Blog Blog { get; set; }
}

Now I change the Blog to have a non-identity key.

public class Blog
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int BlogId { get; set; }
    public string Name { get; set; }

    public virtual List<Post> Posts { get; set; }
}

If I use the Add-Migration command after making the changes it scaffolds an AlterColumn call. But because migrations doesn’t know how to process identity changes the code won’t do anything when I run Update-Database.

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

    public partial class RemoveIdentity : DbMigration
    {
        public override void Up()
        {
            AlterColumn("dbo.Blogs", "BlogId", c => c.Int(nullable: false));
        }

        public override void Down()
        {
            AlterColumn("dbo.Blogs", "BlogId", c => c.Int(nullable: false, identity: true));
        }
    }
}

However, I can replace it with a call to our new operation and everything works as expected.

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

    public partial class RemoveIdentity : DbMigration
    {
        public override void Up()
        {
            this.ChangeIdentity(IdentityChange.SwitchIdentityOff, "dbo.Blogs", "BlogId")
                .WithDependentColumn("dbo.Posts", "BlogId");
        }

        public override void Down()
        {
            this.ChangeIdentity(IdentityChange.SwitchIdentityOn, "dbo.Blogs", "BlogId")
                .WithDependentColumn("dbo.Posts", "BlogId");
        }
    }
}

 


Source Code

If you want to grab the source code to play with… you’ll find it 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

4 Responses to “EF6: Switching Identity On/Off with a Custom Migration Operation”

RSS Feed for RoMiller.com Comments RSS Feed

Hi Rowan:
What a great Bio. I too am a believer. That said, I was wondering if you have any example migration code for F# EF 6.0. I’m having difficulty getting changes to my code-first database using Danial Mohl’s ASPMVC4 F# templates. He pointed me to the Alpha 2 site, but I don’t see a clean way to solve my Enable-Migrations -ProjectName “FSRecordCatalogWebAppSpa” -ContextTypeName “AlbumEntities” commands. I have a blog http://www.fsharpblog.com and I’m trying to publish a step by step guide for my fellow developers who are seeking more analytic power in their mvc 4 web sites. I’ve worked through many issues, but this one seems like it could be a show stopper unless I write my own migration scripts.

Thanks Rowan. I admire and respect your background. What a fantastic testament to a life dedicated to Jesus!

Hi James,
It’s a pleasure to meet you! We don’t include an F# code generator for migrations ‘in the box’, but we do include an extension point where you can customize the existing generators or create your own – http://msdn.microsoft.com/en-us/library/system.data.entity.migrations.dbmigrationsconfiguration.codegenerator(v=vs.103).aspx. If you are interested in contributing to building an F# code generator then I’d be interested in working with you. EF is open source so we could look at contributing it to the core framework or shipping it as a separate plug in on NuGet. Let me know if you are interested.
~Rowan

Hi Rowan: Thank you so much for your prompt reply! I would love to have the opportunity to help with a custom generator. It would be an honor to work with you. So just let me know how you want to proceed. I ready to help. Thanks again Rowan.

James

Hi James,
Great. I’ll get in contact with you via email.
~Rowan


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 146 other followers

%d bloggers like this: