Entity Framework Migrations "Cheat Sheet"

I’m a big fan of Entity Framework (Code First, aka. Magic Unicorn Edition), and in particular I am really beginning to like code first migrations, which makes it easy for you to not only migrate your database step by step, but as an extra bonus also get your database under version control. You don’t have your database version controlled?

Anyway, I mostly do web development and once an application gets going it is minimal how much the database changes from release to release, and since I’m not getting any younger and my memory is not as good as it used to, I often forget some of the (limited) commands that I need to use to generate SQL scripts to run against the server.

So I thought I’d compile a little “cheat sheet” with the basic commands that is necessary to use Entity Framework Migrations.

How to setup a project with Entity Framework Migrations

First step, of course, is to setup a project to actually use Entity Framework Migrations. I always have a dedicated DataAccess project – just a plain old Class Library created with the default template within Visual Studio.

I totally clean it, deleting the default Class1.cs file and then install the Entitiy Framework package, via the Package Manager Console:

PM> Install-Package EntityFramework Installing 'EntityFramework 5.0.0'. You are downloading EntityFramework from Microsoft, the license agreement to which is available at http://go.microsoft.com/fwlink/?LinkId=253898&clcid=0x409. Check the package for additional dependencies, which may come with their own license agreement(s). Your use of the package and dependencies constitutes your acceptance of their license agreements. If you do not accept the license agreement(s), then delete the relevant components from your device. Successfully installed 'EntityFramework 5.0.0'. Adding 'EntityFramework 5.0.0' to EFPlays.DataAccess. Successfully added 'EntityFramework 5.0.0' to EFPlays.DataAccess.

Add some models

I like to divide logical parts of my app into projects, so I also create a dedicated Model class library. To start with an initial model (class) for our first migration, create the following class:

public class User {   public int Id { get; set; }   public string EMail { get; set; }   public string FirstName { get; set; }   public string LastName { get; set; } }

Note: Remember to add reference to the Model project (from the DataAccess project)

Add a DbContext class

The DbContext class is basically a code representation of your database. It defines a number of DbSet properties that you can access from code (using LINQ), but more importantly it tells Entity Framework how to create the database. This is the holy grail of “Code First Development”.

The initial DbContext is very simple:

public class EFPlaysDbContext : DbContext { public DbSet Users { get; set; } }

The DbContext class will be the one used elsewhere in the app, when we need to query the database. But of course, we don’t want to throw this dependency all around the app so the ultimate goal of the DataAccess project is to provide all methods required by the app to query, insert, update and delete from the database.

Enable Migrations

Migrations is actually what this blog post was about, and now we’re getting closer to it.

First off all, we need to enable migrations for the DataAccess project. This is done via the Package Manager Console:

PM> Enable-Migrations Could not load assembly 'EFPlays.DataAccess'. (If you are using Code First Migrations inside Visual Studio this can happen if the startUp project for your solution does not reference the project that contains your migrations. You can either change the startUp project for your solution or use the -StartUpProjectName parameter.)

This error is because I started by creating an ASP.NET MVC project, and it became the startup project. Since I’m not going to use the DataAccess project on its own, instead of changing the startup project to DataAccess I’ll add a reference from the MVC project to both the DataAccess and Model projects.

PM> Enable-Migrations Checking if the context targets an existing database... Code First Migrations enabled for project EFPlays.DataAccess.

This creates for you, a default Configuration class. This class let’s you override EF Code First settings and behavior as well as adding seed data to your database whenever a migration is run.

Adding the first migration

To kick things off, we need to draw a line in the sand. What we’re looking at is migrations, and the first migration contains the entire database schema as it looked when the application was first released and used.

Let’s use the Add-Migration command to create the initial schema:

PM> Add-Migration Initial Scaffolding migration 'Initial'. The Designer Code for this migration file includes a snapshot of your current Code First model. This snapshot is used to calculate the changes to your model when you scaffold the next migration. If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running 'Add-Migration 201308011947555_Initial' again.

This generates a new file, in this case called 201308011947555_Initial (Timestamp (UTC) followed by the name of the migration) – and it’s really simple:

namespace EFPlays.DataAccess.Migrations { using System; using System.Data.Entity.Migrations; public partial class Initial : DbMigration { public override void Up() { CreateTable( "dbo.Users", c => new { Id = c.Int(nullable: false, identity: true), EMail = c.String(), FirstName = c.String(), LastName = c.String(), }) .PrimaryKey(t => t.Id); } public override void Down() { DropTable("dbo.Users"); } } }

A method for deploying the changes (Up) and one for reverting the changes (Down). Within each method, you can also add you own data migration code. Let’s say part of the schema update requires data to be moved from one table, to another. You can do this by calling the SQL method, and specify a SQL statement for migrating the data.

Deploying the database to your local SQL server

Before we deploy our little database to a server, let’s make sure it goes where we want it to go and not let Entity Framework decide for us.

First of all, make sure that the startup project is set to the Web project – not the DataAccess project!

Then, add a connection string to the Web.Config file within the Web project:

Now that we’ve told where the database should be created, let’s kick off the migration. **Note that we’re running the commands against the DataAccess project, even though the Web project is set as the startup project. **(Make sure DataAccess is the Default project in the Package Manager Console toolbat at the top):

PM> Update-Database Specify the '-Verbose' flag to view the SQL statements being applied to the target database. Applying code-based migrations: [201308011947555_Initial]. Applying code-based migration: 201308011947555_Initial. Running Seed method.

If you opening SQL Server Management Studio, you’ll see the EFPlays database containing the one table we defined in our DbContext earlier.

Adding the second migration

We’re now up and running, and the real benefit of Entity Framework Migrations is about to be unleashed. Go ahead and change the User class to include a PhoneNumber property of type string:

public class User { public int Id { get; set; } public string EMail { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public string PhoneNumber { get; set; } }

This is just a small addition to the model, and we want it added to a new migration so let’s create that right away:

PM> Add-Migration 'Add PhoneNumber to User' Scaffolding migration 'Add PhoneNumber to User'. The Designer Code for this migration file includes a snapshot of your current Code First model. This snapshot is used to calculate the changes to your model when you scaffold the next migration. If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running 'Add-Migration 201308012011085_Add PhoneNumber to User' again.

Notice that it’s perfectly valid to use real descriptions as names – just remember the quotation marks.

Deploying the change to the database

This is as easy as calling the Update-Database command:

PM> Update-Database Specify the '-Verbose' flag to view the SQL statements being applied to the target database. Applying code-based migrations: [201308012011085_Add PhoneNumber to User]. Applying code-based migration: 201308012011085_Add PhoneNumber to User. Running Seed method.

Getting hold of a SQL script

When you need to deploy to production, it shouldn’t be done via the Package Manager Console and a Dev machine. Instead, we need a script we can run against the database.

To get the full script of the entire schema, run this command:

PM> Update-Database -Script -SourceMigration:$InitialDatabase Applying code-based migrations: [201308011947555_Initial, 201308012011085_Add PhoneNumber to User]. Applying code-based migration: 201308011947555_Initial. Applying code-based migration: 201308012011085_Add PhoneNumber to User.

You get the script within Visual Studio. Notice that this will create a new table called __MigrationsHistory which is used by Entity Framework to keep track of migrations.

While it is useful to get the full script, most of the time you need a script for the changes made since the last deployment. Let’s say that our Initial migration was already created and now we just want to deploy the one where we added the phone number. This can be done by specifying a SourceMigration:

PM> Update-Database -Script -SourceMigration:Initial Applying code-based migrations: [201308012011085_Add PhoneNumber to User]. Applying code-based migration: 201308012011085_Add PhoneNumber to User.

Entity Framework will in this case generate a script for the changes made from (excluding) the SourceMigration and include all migrations added since. If you want a script for a change made in the middle of the change, you can specify a TargetMigration as well. To try this, first let’s add a new column to our User class – this time we’ll add DateOfBirth:

public class User { public int Id { get; set; } public string EMail { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public string PhoneNumber { get; set; } public DateTime DateOfBirth { get; set; } }

And now we want to add this change as a new migration:

PM> Add-Migration 'Add DateOfBirth to User' Scaffolding migration 'Add DateOfBirth to User'. The Designer Code for this migration file includes a snapshot of your current Code First model. This snapshot is used to calculate the changes to your model when you scaffold the next migration. If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running 'Add-Migration 201308012035580_Add DateOfBirth to User' again.

Back to generating SQL scripts. If we run the previous command now, we’ll get both the ‘Add PhoneNumber to User’ and the ‘Add DateOfBirth to User’. If we wanted only the ‘Add PhoneNumber to User’ migration, the TargetMigration is what we want to use:

PM> Update-Database -Script -SourceMigration:Initial -TargetMigration:'Add PhoneNumber to User' Applying code-based migrations: [201308012011085_Add PhoneNumber to User]. Applying code-based migration: 201308012011085_Add PhoneNumber to User.

Conclusion

The Package Manager Console is your friend. Entity Framework Migrations are really simple for evolving your database from release to release, and at the same time keep your entire database versioned. The versioning part is completely free, you don’t have to do any extra work. Just make sure your evolve your database in small, incremental chunks and you should be fine.