Automating EF 4.3.x Data Migrations in your Build

by rhernandez 13. March 2012 08:55
I was asked to clarify what I meant by “some combinations of parameters not working” so I took it upon myself to rewrite the post to be a bit more specific about the problems that I had when using the migrate.exe tool. https://twitter.com/#!/julielerman/status/179736371353747456

If you are looking for a way to automate the execution of your Entity Framework data migrations in your build process the data team has provided us with a command line tool, migrate.exe,  that exposes all the commands available to us in the Package Manager Console in Visual Studio.  The tool is already conveniently part of the Entity Framework’s NuGet package tool folder.  the tool works as advertised, but it does have some quirks, but first let me show my two favorite ways to utilize the tool. If you are totally new to EF data migrations do visit David Hayden’s blog post with a very basic tutorial on the topic which is simple and to the point, or visit Pluralsight and purchase Julie Lerman’s amazing 1 hour tutorial on the topic (see the details here).

 

Two ways to use Entity Framework’s Migrate.exe

Option 1 – Reusing the app.config

This is the most straight forward option as you are reusing the settings in your applications app.Config to execute the migrations from the command line so the margin for error is relatively small.  The downside is, if you have different connection string settings for all the different environments you want to target with the database migration tool then you will have to come up with a strategy to modify the settings of the app.Config, one possible solution is, if you are working on top of a Web Application project, like an MVC application, you could use web.config transformations during the build process to change application settings.

 

@rem run_db_migrations.cmd
SET CurrentPath=%CD%
SET ConfigFile=%CurrentPath%\Data\App.config
SET MigrateExe=.\packages\EntityFramework.4.3.1\tools\migrate.exe

%MigrateExe% Data.dll /StartUpDirectory:%CurrentPath%\Data\bin\Debug\ /startUpConfigurationFile:"%ConfigFile%"

 

The following table explains the data being passed to the command line tool.

Options Comment
Data.dll The assembly with the DbContext and migrations to be executed.
/StartUpDirectory This is the directory where your assembly is located.
/startUpConfigurationFile This is the path to the configuration file that holds the connection string to be used.
/verbose [Optional] use this option to have the tool output all SQL being executed or generated to the command console.

 

Option 2 – No app.Config

This is by far my favorite approach as you will not need to write complex scripts to modify your app.Config to accomplish targeting multiple environments. I don’t believe there is really a downside to this approach.

 

@rem run_db_migrations.cmd
SET StartUpDirectory=%CD%\Data\bin\Debug\ 
SET ConnectionString=SERVER=.\SQLExpress;DATABASE=Rsvp_Test;TRUSTED_CONNECTION=True
SET ConnectionStringProvider=System.Data.SqlClient
SET MigrateExe=.\packages\EntityFramework.4.3.1\tools\migrate.exe

%MigrateExe% Data.dll /StartUpDirectory:%StartUpDirectory% /ConnectionString:"%ConnectionString%" /connectionStringProvider:%ConnectionStringProvider%

 

The following table explains the data being passed to the command line tool.

Options Comment
Data.dll The assembly with the DbContext and migrations to be executed.
/StartUpDirectory This is the directory where your assembly is located.
/ConnectionString This is the full connection string for your target environment.
/connectionStringProvider This is the ADO.NET provider type to be used when executing against your target environment.
/verbose [Optional] use this option to have the tool output all SQL being executed or generated to the command console.

 

Complaints

I attempted to learn how use this tool by using the command line executable and just messing with the parameters to see which ones where needed and that approach backfired as in some scenarios I wasn’t getting much feedback from the tool as you can see from the following screenshot.  Now, I admit I made the mistake of not providing the connectionStringProvider to the command line tool but I expected it to handle this more gracefully.

image

 

Conclusion

Automating your Entity Framework data migrations in your build server is extremely simple and flexible, so feel free to go version and manage your database the efficient way.

Comments

10/24/2013 11:30:22 PM #

pingback

Pingback from ask.techwikihow.com

deployment – How to migrate Entity Framework DB on publish via MsDeploy | TechwikiHow

ask.techwikihow.com

Comments are closed

The Author



Roberto Hernández

 

Currently a Managing Consultant for Excella in Arlington, VA. I have been architecting and building software solutions using Microsoft technology for the past 14 years. I was awarded as a Microsoft MVP in C# in 2007,2008 and 2010. I am originally from the Dominican Republic, and the proud father of two beautiful daughters that make my life special. You can track me down on twitter (@hernandezrobert) and on this blog http://overridethis.com.


 

Stack Overflow profile for Roberto Hernandez at Stack Overflow, Q&A for professional and enthusiast programmers

INETA Community Speakers Program

Recent Posts