Using Entity Framework Code First Migrations to auto-create and auto-update an application

Last week I came across a tough programming challenge that I couldn’t find solved anywhere on the internet. It took several hours to figure it out, so I thought I’d write it up here to help others.
Gallery Server Pro uses Entity Framework (EF) Code First Migrations to create the database and tables and seed it with initial configuration data. We had these requirements it had to handle:

• Automatically create the database, objects and initial data the first time the application starts or any time it is missing.
• If the database exists but doesn't yet have any gallery-related tables, create them and seed them with data.
• If an earlier version of the gallery data schema is present, automatically upgrade it to the current version. It must support upgrades from any previous version of Gallery Server Pro going back to 3.0.0.

These requirements must be simultaneously met without requiring changes to web.config or any other user input.

The first attempt

The Entity Framework provides two classes that help with database creation and upgrades:

• CreateDatabaseIfNotExists
• MigrateDatabaseToLatestVersion

They both do what their names imply—create the database if it doesn’t exist and upgrade the schema to the latest version. Since I want both of these behaviors, I tried invoking both of them during application startup:

private static void InitializeDataStore()
{
  // Create the database and tables if it doesn't exist.
  System.Data.Entity.Database.SetInitializer(new Data.Migrations.GalleryDbInitializer());
  (new GalleryDb()).Database.Initialize(true);

  // Verify the database has the minimum default records and the latest data schema.
  System.Data.Entity.Database.SetInitializer(new System.Data.Entity.MigrateDatabaseToLatestVersion<GalleryDb, 
System.Data.Entity.Migrations.DbMigrationsConfiguration<GalleryDb>>());
  (new GalleryDb()).Database.Initialize(true);
}

public class GalleryDbInitializer : CreateDatabaseIfNotExists<GalleryDb>
{
  protected override void Seed(GalleryDb ctx)
  {
    SeedController.InsertSeedData(ctx);
  }
}

This seemed to work at first. When the database was missing, it was automatically created and seeded. When a migration was added and applied to an instance running an older version, the Up() method was called as expected. It worked so well this is the code that is included in Gallery Server Pro 3.0 – 3.0.3. These versions had code in the Up() methods that updated values in the tables to change default settings or fix bugs in the UI templates. But we didn’t alter the structure of the database.

That changed with 3.1.0. With this version, for the first time we had to alter the schema of the database by adding a column to one of the tables. I followed the normal steps one takes with EF Code First Migrations:

1. Add the new property to the Code First entity class:
   

   [Table("Metadata", Schema = "gsp")]
   public class MetadataDto
   {
     [MaxLength(1000)]
     public virtual string RawValue
     {
       get;
       set;
     }
   
     // ...Other columns omitted for clarity
   }



2. Use the Package Manager Console to execute ‘Add-Migration v3.1.0’.


3. Verify the new migration class includes code to add the new column:

   namespace GalleryServerPro.Data.Migrations
   {
     using System.Data.Entity.Migrations;
       
     public partial class v310 : DbMigration
     {
       public override void Up()
       {
         AddColumn("gsp.Metadata", "RawValue", c => c.String(maxLength: 1000));
       }
           
       public override void Down()
       {
         DropColumn("gsp.Metadata", "RawValue");
       }
     }
   }

Then, rather than execute Update-Database in the Package Manager Console, I refreshed the web page in the browser. If the MigrateDatabaseToLatestVersion initializer was working correctly, it would notice the database needed upgrading and automatically add the new column. Instead, I received this message:

The model backing the 'GalleryDb' context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269)


The error was occurring just after specifying the CreateDatabaseIfNotExists initializer and before we even had a change to attach the MigrateDatabaseToLatestVersion initializer.

The final solution

At this point I will spare you the hours of investigation and trial and error I went through and just skip to the final solution. In the end, the solution involves four key details:

1. Stop using the CreateDatabaseIfNotExists initializer.


2. Explicitly specify the database schema in the initial migration. Previously I let EF reverse engineer the data schema from the Code First entity classes, leaving the Up() method empty except for a few custom indexes. Now we need the initial migration to specify the schema that exists at that version (before any schema changes that are introduced in later migrations). Essentially that means adding a bunch of CreateTable() function calls. For example:
   

   public partial class v300 : DbMigration
   {
     public override void Up()
     {
       CreateTable(
         "gsp.Album",
         c => new
         {
           AlbumId = c.Int(nullable: false, identity: true),
           FKGalleryId = c.Int(nullable: false),
           FKAlbumParentId = c.Int(),
           DirectoryName = c.String(nullable: false, maxLength: 255),
           ThumbnailMediaObjectId = c.Int(nullable: false),
           SortByMetaName = c.Int(nullable: false),
           SortAscending = c.Boolean(nullable: false),
           Seq = c.Int(nullable: false),
           DateStart = c.DateTime(),
           DateEnd = c.DateTime(),
           DateAdded = c.DateTime(nullable: false),
           CreatedBy = c.String(nullable: false, maxLength: 256),
           LastModifiedBy = c.String(nullable: false, maxLength: 256),
           DateLastModified = c.DateTime(nullable: false),
           OwnedBy = c.String(nullable: false, maxLength: 256),
           OwnerRoleName = c.String(nullable: false, maxLength: 256),
           IsPrivate = c.Boolean(nullable: false),
         })
           .PrimaryKey(t => t.AlbumId)
           .ForeignKey("gsp.Gallery", t => t.FKGalleryId, cascadeDelete: true)
           .ForeignKey("gsp.Album", t => t.FKAlbumParentId)
           .Index(t => t.FKGalleryId)
           .Index(t => t.FKAlbumParentId);
   
       // ... Rest of tables omitted for clarity
     }
   }



3. Replace the Initialize call with a call to DbMigrator.Update(). The new InitializeDataStore function looks like this:
   

   private static void InitializeDataStore()
   {
     System.Data.Entity.Database.SetInitializer(new System.Data.Entity.MigrateDatabaseToLatestVersion<GalleryDb, GalleryDbMigrationConfiguration>());
   
     var configuration = new GalleryDbMigrationConfiguration();
     var migrator = new System.Data.Entity.Migrations.DbMigrator(configuration);
     if (migrator.GetPendingMigrations().Any())
     {
       migrator.Update();
     }
   }

   
   

   public sealed class GalleryDbMigrationConfiguration : DbMigrationsConfiguration
   {
     protected override void Seed(GalleryDb ctx)
     {
       MigrateController.ApplyDbUpdates();
     }
   }



4. Move record updates from the Up() methods of migrations and the initial data seeding to GalleryDbMigrationConfiguration.Seed(). The Seed() method is called after all migrations are complete, which is the first time you can successfully use the Code First entity classes to query and update the database. Any earlier—such as during each migration’s Up() method—and you’ll get an exception if you reference an entity class whose properties don’t match the table’s definition.

With this configuration, the database is

• automatically created and seeded whenever it is missing
• updated to the latest version when an older data schema is detected
• backwards compatible with a database created with the original EF architecture

The next version of Gallery Server Pro will include these changes, so when it’s released you can download the source to see it in action. You can also read more about how Code First is used and other technical aspects in the Code Project article Gallery Server Pro - An ASP.NET Gallery for Sharing Photos, Video, Audio and Other Media. Hopefully this will save some of you the grief I went through. Cheers!