All Blog Posts

Entity Framework Part 3: Migrations

If you haven't yet, read part 1 and part 2 in this Entity Framework series.

One thing that has always bothered me is how difficult it is to easily deploy database changes with your application updates. The classic approach is to use a tool such as Red Gate SQL Compare to determine the differences in the schemas between your testing and production environments and create a DDL script to manually deploy. If you have data changes, you’ll either need to keep track manually (error prone) or use a tool like Red Gate SQL Data. Even with the costly tools you can still have issues that require human interaction, which adds a ton of “ceremony” to your deployments. To have a fluid Continuous Integration environment, you must find a way to tackle this issue. Entity Framework Migrations

Entity Framework Code First has a feature called Migrations that make database deployments a breeze. It’s built into Entity Framework so there’s minimal friction. It’s code-based so it’s flexible. Since it’s part of Entity Framework, one can assume that it will always be up-to-date with all the new features that will be added.

There are two flavors of Migrations: Automatic Migrations and Code-Based Migrations. Automatic Migrations is extremely easy to use and very powerful. I use Automatic Migrations in most of my projects; however, I’ve recently run into some flexibility issues as my projects mature and I’ve made it a point to learn Code-Based Migrations which opens up more possibilities. I’ll attempt to explain all of this in this post. I’ll also show how you can integrate Migrations into your TeamCity build process to achieve Continuous Integration in a Web Application.

Automatic Migrations

Follow along with my sample code here on Github, or click on any image to see the code.

Using a migration framework in itself is a large topic, so I’m just going to jump right into it and try to explain as I go. Following the code above would probably be very helpful, as well as working along with your own sample project.

When working with Migrations, you’ll often be using the NuGet Package Manager Console. If it’s not already added to your IDE, you can add it by going to View => Other Windows => Package Manager Console. When the console is active, you’ll always want to make sure the project that holds your Context class is set as the Default Project.

In the console, let’s run Enable-Migrations –EnableAutomaticMigrations. This will create a Migrations subfolder in your project that contains a Configuration class. This Configuration class will allow us to set options for Migrations as well as seed data.
Default Automatic Migrations Configuration

Using Migrations is going to eliminate the need for us to use our DatabaseInitializer class, so I’m going to copy the Seed method to our new Migrations Configuration Seed method and get rid of DatabaseInitializer. The problem with this is that this Seed method is going to run each time we run migrations, so we need to add some logic to ensure the Seed data isn’t going to add duplicate records. This can be a pain point because your flexibility is really limited, especially if you need to modify some of the data that your Seed method has already created. In this demo I’m going to simply comment/uncomment as needed and I’ll show you a better way of handling this using Code-Based Migrations.

Now let’s view Migrations in action. At this point we have an existing database with populated data. We want to make some changes to our model and have Migrations automatically make the changes in the database. I’ve added an Email property to the Author class and now I’m going to run Update-Database in the console to make the change to the database. In doing this, I see that the Email field has been added to the Author table.
Modified Author Class

I don’t really want to show much more with Automatic Migrations because I feel it isn’t a prudent route to choose. It’s easy to use and very basic, but all of my projects seem to have outgrown the functionality, and I need to modify them to use Code-Based Migrations. If you have a specific Automatic Migrations question, feel free to leave a comment and I’ll try to help where I can.

Code-Based Migrations

Sample code is here on Github.

In my opinion, mastering Code-Based Migrations is where we should be focusing our efforts. It provides us better versioning abilities so we can better manipulate data. It gives us easy methods to add Indexes and Stored Procedures to our database (functionality that is missing in Automatic Migrations). Finally, it gives us an easy method to see how our database schema has changed over time, which can be very helpful when trying to learn a system or troubleshoot an issue.

Try as I might, I couldn’t come up with better instructions than I found in Microsoft’s Data Developer Center. I’m going to follow those instructions to enable migrations and create my initial migration. I also added seeding of initial data in this method. Note how we use SQL syntax in these migrations to make it resilient to a changing model structure:
EF Initial Code Based Migration

Next, I will add the Email property to my Author class and execute add-migration AddEmailToAuthor to get this:
EF Add Email to Author

In looking at the above file, we see that Migrations is able to detect our new field and adds that automatically to the migration. That is pretty darn useful. It also opens our new migration file and gives us an opportunity to initialize any data in the case of a new non-nullable field or a field rename. We just have to make sure to execute update-database when we’re done to actually make the changes in our database.

I’m going to create one last migration to do a few random things. After doing this a few times, I am now definitely convinced that Code-Based Migrations is the way to go because I was jumping through multiple hoops to accomplish this functionality with Automatic Migrations. Here’s my last migration file:
Entity Framework Code-based Migration

Here are a few random notes about using Code-Based Migrations:
  1. When you generate a new migration file, Entity Framework will create a new class in your Migrations subfolder. The class names will contain the name you’ve given to the migration and will have a date/time stamp pre-pended. This will tell Migrations in which order to run your migration files.
  2. Migrations uses a __MigrationHistory system table to determine which migrations have been applied. Each Code-Based Migration should have a record in this table. This is how Migrations knows which migrations have been applied.
  3. If you’re more hands on and want to see what Migrations is actually doing, you can add the –verbose switch to Update Database, like so: update-database –verbose.

Entity Framework Migrations and TeamCity (Web Applications Only)

At Far Reach we use TeamCity as our Deployment Platform. Before adopting Migrations and automating our database deployments, we weren’t really seeing a lot of value in automated deployments because we often had to manually deploy database scripts. Now that we’re fully automated, we can do deployments quickly and the element of human error has been removed. We can also do cool things like scheduling deployments during non-peak hours.

There are plenty of examples on the web showing how to execute Migrations on application startup. We originally went this route, but found two issues: Migrations was using too many resources on our production web server, and it wouldn’t actually run until the first user visited the page (we could have automated this part, but felt there was a more direct approach). We decided that the build server was a better candidate to run Migrations so we decided to integrate with TeamCity.

Unfortunately, I haven’t found a TeamCity plugin for Entity Framework Migrations, so we have to do a bit of work to make this happen. The first thing we need to do is add migrate.exe from the tools subfolder in the Entity Framework NuGet package to our project. In the Properties window of migrate.exe we need to set “Copy to output directory” to “Copy if newer.” This will include migrate.exe in our build and will make it available to TeamCity.

Now we need to perform the following steps in TeamCity so it will trigger Migrations on deployment:
  1. Add a new Command Line step to your Build Configuration.
  2. Set the Working Directory to your bin subfolder.
  3. Use the following Custom Script:
Teamcity Migrations

Bingo! Now that TeamCity is running Migrations, we want to make sure that our application is not automatically executing Migrations. We want to check our code to ensure we don’t have any initializers running. I’ve also seen initializers in the web.config file so we also want to check in there.

Wrap It Up!

There’s no silver bullet for database deployments and Continuous Integration. This approach works well for us and is fairly easy to use. I would be very interested in discussing your approach in an effort to improve ours, so let’s talk shop.

My next post is going to cover some of the features in the Entity Framework Power Tools Pack, including Reverse Engineer Code First. If you don’t have the luxury of starting your application from scratch, this will contain a lot of useful info for you.

Link Dump

Pluralsight videos on EF Migrations  

FluentMigrator project on GitHub – if EF isn’t your thing, maybe this could help

General tips and tricks for EF Migrations

 View Part 4 on Reverse Engineer Code First