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


Posts By Year

2023 (5)
2022 (10)
2021 (5)
2020 (12)
2019 (6)
2018 (8)
2017 (11)
2016 (6)
2015 (17)
2014 (2)
2013 (4)
2012 (2)

Posts By Category

.NET (4)
.NET Core (2)
AWS (5)
AWS API Gateway (1)
Android (1)
Apache Camel (1)
Architecture (1)
Audio (1)
Azure (2)
Book review (3)
Business (1)
C# (3)
C++ (2)
CloudHSM (1)
Containers (4)
Corporate culture (1)
Database (3)
Database migration (1)
Desktop (1)
Docker (1)
DotNet (3)
DotNet Core (2)
ElasticSearch (1)
Entity Framework (3)
Git (3)
IIS (1)
JDBC (1)
Java (9)
Kibana (1)
Kubernetes (1)
Lambda (1)
Learning (1)
Life (7)
Linux (1)
Lucene (1)
Multi-threading (1)
Music (1)
OData (1)
Office (1)
PHP (1)
Photography (1)
PowerShell (2)
Programming (28)
Rants (5)
SQL (2)
SQL Server (1)
Security (2)
Software Engineering (1)
Software development (2)
Solr (1)
Sql Server (2)
Storage (1)
T-SQL (1)
TDD (1)
TSQL (5)
Tablet (1)
Technology (1)
Test Driven (1)
Unit Testing (1)
Unit Tests (1)
Utilities (3)
VC++ (1)
VMWare (1)
VSCode (1)
Visual Studio (2)
Wallpapers (1)
Web API (2)
Win32 (1)
Windows (9)
XML (2)

Posts By Tags

.NET(6) API Gateway(1) ASP.NET(4) AWS(3) Adults(1) Advertising(1) Android(1) Anti-forgery(1) Asynch(1) Authentication(2) Azure(2) Backup(1) Beliefs(1) BlockingQueue(1) Book review(2) Books(1) Busy(1) C#(4) C++(3) CLR(1) CORS(1) CSRF(1) CTE(1) Callbacks(1) Camel(1) Certificates(1) Checkbox(1) CloudHSM(1) Cmdlet(1) Company culture(1) Complexity(1) Consumer(1) Consumerism(1) Containers(3) Core(2) Custom(2) DPI(1) Data-time(1) Database(4) Debugging(1) Delegates(1) Developer(2) Dockers(2) DotNetCore(3) EF 1.0(1) Earphones(1) Elastic Search(1) ElasticSearch(1) Encrypted(1) Entity framework(1) Events(1) File copy(1) File history(1) Font(1) Git(2) HierarchyID(1) IIS(1) Installing(1) Intelli J(1) JDBC(1) JSON(1) JUnit(1) JWT(1) Java(3) JavaScript(1) Kubernetes(1) Life(1) LinkedIn(1) Linux(2) Localization(1) Log4J(1) Log4J2(1) Lucene(1) MVC(4) Management(2) Migration history(1) Mirror(1) Mobile Apps(1) Modern Life(1) Money(1) Music(1) NGINX(1) NTFS(1) NUnit(2) OData(1) OPENXML(1) Objects(1) Office(1) OpenCover(1) Organization(1) PHP(1) Paths(1) PowerShell(2) Producer(1) Programming(2) Python(2) QAAC(1) Quality(1) REDIS(2) REST(1) Runtimes(1) S3-Select(1) SD card(1) SLF4J(1) SQL(2) SQL Code-first Migration(1) SSH(2) Sattelite assemblies(1) School(1) Secrets Manager(1) Self reliance(1) Service(1) Shell(1) Solr(1) Sony VAIO(1) Spirituality(1) Spring(1) Sql Express(1) System Image(1) TDD(1) TSQL(3) Table variables(1) Tables(1) Tablet(1) Ubuntu(1) Url rewrite(1) VMWare(1) VSCode(1) Validation(2) VeraCode(1) Wallpaper(1) Wallpapers(1) Web Development(4) Windows(2) Windows 10(2) Windows 2016(2) Windows 8.1(1) Work culture(1) XML(1) Yii(1) iTunes(1) renew(1) security(1)