Home Software Blog Wallpapers Webtools
Copy over EF migration entries
Saturday 14, February 2015   |   Post link   |   Sample code  Download related meterial
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

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'); 

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'); 
Running this on the remote database will create the missing entries which EF requires.
Sample code  Download related meterial


Posts By Year

2017 (8)
2016 (6)
2015 (18)
2014 (2)
2013 (4)
2012 (2)

Posts By Category

Android (1)
Book review (3)
Business (1)
Containers (3)
Corporate culture (1)
Database migration (1)
Desktop (1)
Entity Framework (2)
Git (2)
IIS (1)
Java (2)
Life (6)
Lucene (1)
OData (1)
Office (1)
PHP (1)
PowerShell (1)
Programming (14)
Rants (5)
Software Engineering (1)
Software development (1)
Solr (1)
Sql Server (1)
T-SQL (1)
TDD (1)
TSQL (5)
Tablet (1)
Technology (1)
Test Driven (1)
Unit Testing (1)
Utilities (1)
Wallpapers (1)
Windows (5)
XML (1)

Posts By Tags

ASP.NET(4) Adults(1) Advertising(1) Android(1) Anti-forgery(1) Backup(1) Beliefs(1) Book review(2) Books(1) Busy(1) C#(2) C++(1) CSRF(1) CTE(1) Checkbox(1) Cmdlet(1) Commons(1) Company culture(1) Consumerism(1) Containers(3) Data-time(1) Database(1) Debugging(1) Developer(2) Dockers(2) Entity framework(1) File copy(1) File history(1) Git(1) GradleApache(1) HierarchyID(1) IIS(1) Installing(1) Intelli J(1) JSON(1) JavaScript(1) Log4J(1) Lucene(1) MVC(4) Management(2) Migration history(1) Mobile Apps(1) Modern Life(1) Money(1) OData(1) Office(1) Organization(1) PHP(1) Paths(1) PowerShell(1) Programming(1) Python(1) Quality(1) SD card(1) SQL(1) SQL Code-first Migration(1) SSH(1) School(1) Self reliance(1) Solr(1) Sony VAIO(1) Spirituality(1) Sql Express(1) System Image(1) TDD(1) TSQL(3) Tablet(1) Url rewrite(1) Validation(2) Wallpapers(1) Web Development(4) Windows(1) Windows 10(1) Windows 2016(2) Windows 8.1(1) Work culture(1) XML(1) Yii(1)