Using Entity Framework Code First with an existing database: 2

Ok - this is the big one: this is where I enrol the Migrations aspect of Code First to manage changes to the model/database. I'm pretty sure that prior to EF 4.3 this whole next step would have been impossible with an existing database. To be able to use Migrations we have to simulate the initialization of the database, even though this database was 'initialized' ages ago, when I first created it.

One thing to pay attention to before you start: despite the fact that your app may already have a Web.config with your newly-created context as a named connectionString, it's the App.config that matters when you're working with the the console. This lost me a bit of time, to my chagrin, until I understood what was going on. Despite the fact that the Default Project was set to ConnemaraNet (my MVC website project) in the console, any commands ('Add-Migration' or 'Update-Database') would only execute against a SQLEXPRESS instance, which was no use to me. I could see this by using the -Verbose flag when running those commands. Once I added the connectionString to the App.config, all was gravy.

Executing the 'Enable-Migrations -EnableAutomaticMigrations' command in Package Manager Console ('the console') adds a Migrations folder to the project, with the following Configuration class:

namespace ConnemaraNet.Migrations
{
    internal sealed class Configuration : DbMigrationsConfiguration
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
            AutomaticMigrationDataLossAllowed = true;
        }
        protected override void Seed(ConnemaraNetContext context)
        {
        }
    }
}

Setting the baseline

In the console the command 'Add-Migration Initial [-StartUpProjectName 'Your.FullyQualified.ProjectName']' creates a migration class (inheriting from DbMigration) based on your model in a Migrations folder in your app (if your solution has more than one startup project you'll have to qualify every command with the -StartUpProjectName as I only found out when I tried all this at work; at home my project was rather simple so it didn't need it). But, as Julie Lerman points out, you want to remove all the code business from this class, leaving only two empty stubs.

namespace ConnemaraNet.Migrations
{
    using System.Data.Entity.Migrations;
    public partial class Initial : DbMigration
    {
        public override void Up()
        {
        }
        public override void Down()
        {
        }
    }
}

Running 'Update-Database' in the console will now create the '__MigrationHistory' table, which gives me a snapshot, a starting point, a point from which Code First can manage my database while I tend lovingly to my entity model as if it was a zen garden, using minor code changes as a tiny rake.

This time the baby stays

So what exactly happens when you change the model from now on? What else do I have to do? I added an extra field, 'MiddleName', to one of my entity classes, the Author class. Now if I run a test that selects all authors, for example, from this table, I'll get an error because my model is out of whack with my database. Ok, that makes sense. Running 'Update-Database' rectifies this by adding the offending field to the 'Author' table. But the magic part is that it left the data alone. This is the new part to me: I've only ever seen Code First handle model/table synchronisation by dropping and recreating the database, what I call the 'ThrowingTheBabyOutWithTheBathwater' strategy. This time the baby stays! This is what just happened:

PM> Update-Database -Verbose
No pending explicit migrations.
Applying automatic migration: 201202260331399_AutomaticMigration.
ALTER TABLE [Author] ADD [MiddleName] [nvarchar](100)
[Inserting migration history record]
PM >

This is so exciting! It's worth repeating using bad language: the frickin' database got updated using f*&#in' Code First, and it left the effin' data alone! Back of the net!

Added 'MiddleName' column AND left the existing data there. Niiiice

If you're using Visual Studio Server Explorer to do most of your database inspection and query execution etc., you won't actually see a System Tables folder, which is where Code First creates '__MigrationHistory', but no matter: this table, which in EF 4.3 replaces the antediluvian 'EdmMetadata' table will just show alongside your regular tables when it gets created.

All in all, not a bad day's work. EF 4.3 Migrations allows me to ride the Code First bullet train without forfeiting my existing database, and as a bonus I got away without having to install SQL Server 2008 Management Studio - I seem to be able to do it all with Server Explorer and Package Manager Console. There's a fair bit more to Migrations that you can read about on the ADO.Net team blog, specifically around the differences between Automatic and Code-based migrations. In this post I just wanted to explain how I got Entity Framework Code First to play nice with my trusty old SQL Server database.