swirl
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:

context-changed-exception.png

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.
Sample code  Download related meterial


Load Comments

Posts By Year

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

Posts By Category

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

Posts By Tags

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