Code through the pain Ladislav Mrnka's professional blog about software development

Julie Lerman recently wrote a very nice article about using Entity Framework 4.3 code first migrations with an existing database. Using an existing database is very common scenario so this task will be used often. The mentioned article contains one cumbersome operation where we must create the initial migration and manually clean all code prepared for us because we are using the existing database where these changes must not be executed (it would result in an exception and stop our migration process). ADO.NET team promptly addressed this issue and Entity Framework 4.3.1 released yesterday contains a new command parameter which will create the empty initial migration for us.

The rest of the article will show the whole walkthrough of adding the initial migration and it will especially targets the situation where we are upgrading existing database created by the code first approach with Entity Framework 4.1 or Entity Framework 4.2.

Initial database

Lets start with some initial application and database created with Entity Framework 4.1. Suppose that we used this code to create our initial database:

public class Program
{
    static void Main(string[] args)
    {
        using (var context = new Context())
        {
            context.Database.Initialize(false);
        }
    }
}

public class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public virtual ICollection<Address> Addresses { get; set; }
}

public class Address
{
    public int Id { get; set; }
    public int AddressTypeId { get; set; }
    public string Street { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
    public string PostalCode { get; set; }
}

public class Context : DbContext
{
    public DbSet<Person> People { get; set; }
    public DbSet<Address> Addresses { get; set; }
}

Now when we have our initial code we can execute the application and it will create database which will contain these tables:

Once we have our initial database we are ready to upgrade to Entity Framework 4.3.1 and start using Entity Framework code first migrations.

Upgrading to Entity Framework 4.3.1 and enabling migrations

Entity Framework 4.3.1 is available only as a NuGet package. To install the package in expected way we need Internet connection and NuGet Package Manager available on Visual Studio Gallery. If you don't have access to Internet from your development machine check this article from Arthur Vickers for alternatives.

Installing the last version of Entity Framework in Package Manager Console:

Install-Package EntityFramework

Whole migrations tool set in Visual Studio is controlled through issuing commands to Package Manager Console (PowerShell). When adding migrations to an existing database we face the single issue - our database doesn't contain __MigrationHistory table required by migration toolset. This table contains information about already executed migrations and it is created when we create database with Entity framework 4.3 (or newer) using default database initializer or when we execute migration for the first time.

Enabling automatic migrations in Package Manager Console:

Enable-Migrations -EnableAutomaticMigrations -ProjectName UpgradeTest

Enabling automatic migrations will add a new Migrations folder to our project (named UpgradeTest in this case) and a new class configuring our migrations infrastructure. To better understand details you can check two walkthroughs provided by ADO.NET team (Automatic migrations, Code-Based migrations). The behavior will be the same with code-base migrations because we don't have __MigrationHistory table in the database yet.

Creating and executing our initial migration

Now when we have everything prepared we need to add initial migration to our project. To do that, we will use a new feature in Entity Framework 4.3.1 - a new IgnoreChanges parameter of Add-Migration command.

Adding initial migration in Package Manager Console:

Add-Migration InitialMigration -IgnoreChanges

Using the new parameter will enforce migration tool set to create a completely empty migration instead of scaffolding all entities in our model so we don't need to go through the created code and remove all table creations or removals. The empty migration is needed because it reflects the situation where the initial migration doesn't create anything (all tables needed for the current model version already exist) except __MigrationHistory table.

If we are upgrading the database created by the code first approach from previous version of Entity Framework (4.1 or 4.2) we may also want to remove EdmMetadata table which is not needed since Entity Framework 4.3. To remove the table we can add simple modification to our initial migration:

Modified migration removing obsolete EdmMetadata table:

public partial class InitialMigration : DbMigration
{
    public override void Up()
    {
        // Use this only when upgrading from EF 4.1 or 4.2
        Sql("DROP TABLE EdmMetadata");
    }

    public override void Down() { }
}

As the last step we need to execute our InitialMigration to remove EdmMetadata table from our database and create new __MigrationHistory table.

Executing a migration in Package Manager Console:

Update-Database

After executing the migration the database will not contain EdmMetadata table but instead __MigrationHistory table will be added to system tables.

The important aspect of the whole walkthrough is that we must create initial migration before we start doing any changes to our model (entities) => to make this work our current code must be in sync with our current database. It the model is not in sync with the database our current change set will not be reflected in the database (remember we are using an empty migration which says that our model doesn't contain any changes) or we will get exception as mentioned in this question asked by Pouyan on Stack Overflow. If we don't have our code in sync we should grab the last suitable version from the source control (you are using any, aren't you?) and use it as a new working copy where initial migration will be created. After creating the initial migration on the new working copy we can merge all our changes from the previous working copy to this one.

Now both our application and our database are ready for further development which will be covered and processed by the migrations tool set.