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 
.
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:
- Drop all foreign key constraints that point to the primary key we are changing
- Drop the primary key constraint
- Rename the existing column (so that we can re-create the foreign key relationships later)
- Add the new primary key column with the new identity setting
- 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
- Drop old primary key column
- Add primary key constraint
- 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.
romiller.com 