Copy over EF migration entries

Saturday 14, February 2015 | Post link | Sample code

Entity Framework's code first migration changes the way developers have been approaching the database layer. Code-first migration makes one think in terms of object/classes also known as the 'model' which is first developed and then automates the generation of the database tables to hold the data.
As you keep making changes to the model, using the database-migration feature the database is kept up-to-date with changes in the model.
Consider a situation where an application runs on the developer's local machine along with local SQL server and the same application runs on a remote machine connecting to a remote SQL server. Once model changes are made locally and everything is running fine, we'd like to move these changes to the remote setup. However, when we copy the bits to the remote machine, we see the following exception:

This happens because of code-first migration. EF always crosschecks the model against the database which it is connecting to. What is needed is to copy the new entries in the __MigrationHistory table from the local SQL database to the remote database. The tricky part is copying the 'Model' column which is of type VarBinary(MAX). To copy this, we can use Base64 encoding/decoding. Here is a script which creates an INSERT SQL script to create missing entries in the target __MigrationHistory table.

declare @counter int = 0
declare @rows int = 0
declare @sql varchar(max) = ''
declare @base64 varchar(max)
declare @model varbinary(max)
declare @migrationId varchar(max) = ''
declare @contextKey varchar(max)
declare @version varchar(max)
declare @sqllines table (line varchar(max))

select @rows = count(*) from __MigrationHistory
insert into @sqllines select 'declare @decoded varbinary(max);' + char(10) + 
'declare @base64 varchar(max);' + char(10) + 
'declare @binary varbinary(max);' + char(10);

while(@counter < @rows) begin
select 	top(1) @migrationId = MigrationId, @contextKey = ContextKey,  @model = Model, @version = ProductVersion 
from __MigrationHistory where MigrationId > @migrationId 
	
select @base64 = cast('' as xml).value('xs:base64Binary(sql:variable("@model"))', 'varchar(max)')
set @sql = 'if not exists (select * from __MigrationHistory where MigrationId = ''' + @migrationId + ''') begin
	set @base64 = ''' + @base64 + '''; 
	set @binary = cast('''' as xml).value(''xs:base64Binary(sql:variable("@base64"))'', ''varbinary(max)'');
	insert into __MigrationHistory values(''' + @migrationId + ''', ''' + @contextKey + ''', @binary, ''' + @version + '''); ' + char(10) + 'end' + char(10);
	insert into @sqllines select @sql
set @counter = @counter + 1
end

select * from @sqllines

The output is a SQL script like:

declare @decoded varbinary(max);
declare @base64 varchar(max);
declare @binary varbinary(max);

if not exists (select * from __MigrationHistory where MigrationId = '201501042302152_Initial') begin
	set @base64 = 'H4sIAAAAAAAEAO1cX2/bOBJ/P+C+g6Cn3...'; 
	set @binary = cast('' as xml).value('xs:base64Binary(sql:variable("@base64"))', 'varbinary(max)');
	insert into __MigrationHistory values('201501042302152_Initial', 'MyDbContext', @binary, '6.1.1-30610'); 
end

if not exists (select * from __MigrationHistory where MigrationId = '201501190103335_Comments') begin
	set @base64 = 'H4sIAAAAAAAEAO1d3W7juhG+L9B3EHTVFjlxkkWA08A+B1knaYN...'; 
	set @binary = cast('' as xml).value('xs:base64Binary(sql:variable("@base64"))', 'varbinary(max)');
	insert into __MigrationHistory values('201501190103335_Comments', 'MyDbContext', @binary, '6.1.1-30610'); 
end

Running this on the remote database will create the missing entries which EF requires.

Download related material

Categories: Database migration (1) Entity Framework (3) TSQL (5)

Tags: Migration history(1) SQL Code-first Migration(1)

Comments