Sunday, February 26, 2012

Using Entity Framework Code First with an existing database: 2

To recap: I recreated my database as a Code First POCO layer (by hand!) in Part 1, and now I need to migrate changes to the entity model to my database.

See also:

Switching on EF Migrations

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.

14 comments:

  1. An interesting and quite imformative read. Working with code first right now - code first + existing db. Trying to make it all run with proper entity configuration and initialization... kind of afraid of a giant cluster f

    ReplyDelete
  2. Great article! Can you elaborate on the app.config required settings? Maybe steps?

    ReplyDelete
  3. App.config settings are simply the connection string to your database!

    ReplyDelete
  4. Good article!, i had a problem because my start project was another than the EF project.

    The error was calling update-database and add-migration and was System.Reflection.TargetInvocationException.

    I added the parameter -StartupProjectName to both calls and everything worked fine.

    ReplyDelete
    Replies
    1. Most likely because it uses the Startup project config file for the connectionstring. It's a common way to put your settings in the startup project. If you'd put "-verbose" behind the command you would see that it's using the startup project.

      Delete
  5. Thanks for the quick and easy tutorial. Made my life a lot easier when I had to add a warehouse db to my application that was created for reporting.
    I ran into a few snags along the way but they were mostly because I have 2 database instances using Code First in my solution.

    ReplyDelete
  6. Works great with my smarterasp.net trial account. Thank you thank you =)

    ReplyDelete
  7. This is a bit like using dbmaintain (http://dbmaintain.sourceforge.net)
    to manage script deployments - except of course with that tool you have to write SQL yourself, and of course if you don't get it right the first time, things will get more interesting, as there's no rollback to a previous version.

    I've played with targeting specific configuration versions in EF, migrating then rolling back to an earlier state, and using the Seed function to initialize data, eg.

    protected override void Seed(CodeFirstNewDbSample.BlogContext context)
    {
    // This method will be called after migrating to the latest version.
    context.Blogs.AddOrUpdate(
    b => b.BlogId,
    new Blog { Name = "A Blog (seeded)" },
    new Blog { Name = "B Blog (seeded)" }
    );
    }

    I've found one case where it added the values multiple times, but unfortunately I can't replicate it (?)
    I had some issues with .Net Framework and EF versions before I got Migrations working too but this all looks very promising, at least against SQL Server.

    ReplyDelete
    Replies
    1. I haven't done any explicit Up-ing or Down-ing yet, Rob, sounds like I need to blog it to understand it. Writing about stuff is a great way to set yourself to understanding it. Thanks for the comment.

      Delete
  8. AutomaticMigrationDataLossAllowed = true;

    This ensures your data gets wiped out so I don't know how this article is accurate...

    ReplyDelete
    Replies
    1. No, it allows for *some* data to be lost, if that's what you want, in order to update your db.

      Delete
  9. many thank for sharing this article... i like it

    ReplyDelete
  10. FWIW, Julie's link moved here:

    http://thedatafarm.com/data-access/using-ef-4-3-code-first-migrations-with-an-existing-database/

    Thanks for the great article

    ReplyDelete