Migrate Entity Framework 4.1 database to EF5 for upgrade, create new database for new installations

I am currently developing version 2 of the application with an existing codebase for version 1 users, but I also need to deploy the new version to new users (i.e. clean installs). Version 1 uses Entity Framework 4.1. Version 2 uses Entity Framework 5.

My problem is that I need to update existing version 1 users to version 2 and migrate their database to the new version. The new schema adds a number of new tables, leaves a couple of existing tables untouched, and leaves other tables.

In my travels, I found that EF4.1 does not contain a table _MigrationHistory

. So I went back to the original version 1 code, updated it to EF5 and did the initial migration like this:

Add-Migration Initial -IgnoreChanges

      

        
        
        
      

    

This works well to get the original version 1 (EF4.1) of the database ready for migration (i.e. add the table _MigrationHistory

). Then I migrate that to my version 2 code and run:

Update-Database

      

        
        
        
      

    

which creates the table _MigrationHistory

and then I run:

Add-Migration Version2

      

        
        
        
      

    

which creates a nice database migration to version 2.

This works great if the database already exists in either schema version 1 or schema version 2 (in the latter case no migrations are applied), since I am using the following initializer:

Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyDbContext, Configuration>());

      

        
        
        
      

    

However, if the database does not exist, the migration Initial

succeeds, but the migration Version2

fails because some of the migration steps drop tables, indexes, and foreign keys that obviously do not exist in the empty database.

So, I'm completely at a loss - how do I migrate existing version 1 users to version 2 (and keep their data), but also support new installations that need to completely create a database?

I've seen a lot of ideas about creating and running SQL scripts for migration, but I really need it to be done automatically when my application starts. End users really only need to run the MSI file to update.

UPDATE : here's the solution suggested below by w.brian (sorry, I can't vote yet):

string connectionString = ConfigurationManager.ConnectionStrings["DbContext"].ConnectionString;
if (Database.Exists(connectionString))
{
    Database.SetInitializer(new MigrateDatabaseToLatestVersion<DbContext, Migrations.Configuration>());
}
else
{
    Database.SetInitializer(new DefaultDataInitialiser());
}

      

        
        
        
      

    

UPDATE2 . The above seems to work for the first time with the new create. However, the next run passes the test Database.Exists()

and tries to migrate the database. However, since _MigrationHistory

there is only one record in the table (generated by the new / clean install) and the migration has two records (one for the EF4.1 migration, one for the new schema), the migration logic starts from the beginning and tries to apply each migration.

It fails as it tries to create tables that are already present in the database - they created them as part of a clean install!

So the gist of it is that this approach breaks the migration logic.

UPDATE3 . So I think I figured out how to create the original database and allow subsequent runs to only apply the migration. Above, if the database already exists, continue with the migration code.

If the database does not already exist, use the method DefaultDataInitialiser

and in Seed()

, follow these steps:

context.Database.ExecuteSqlCommand(@"DELETE FROM [__MigrationHistory]");

byte[] version1Model = ConvertHexStringToByteArray("1F8B...");
context.Database.ExecuteSqlCommand(@"INSERT INTO [__MigrationHistory]
                                        ([MigrationId]
                                        ,[Model]
                                        ,[ProductVersion])
                                    VALUES
                                        ('201302082120145_Initial'
                                        ,{0}
                                        ,'5.0.0.net40')", new object[] { version1Model });

byte[] version2Model = ConvertHexStringToByteArray("1F8B...");
context.Database.ExecuteSqlCommand(@"INSERT INTO [__MigrationHistory]
                                        ([MigrationId]
                                        ,[Model]
                                        ,[ProductVersion])
                                    VALUES
                                        ('201302082200350_Version-2'
                                        ,{0}
                                        ,'5.0.0.net40')", version2Model);

      

        
        
        
      

    

This results in the new database being "completely migrated" in terms of the migration logic. The next time the app is launched, the database exists and the magic MigrateDatabaseToLatestVersion

starts, sees that the database is on the latest version (or in the future is updated to the next migration) and happy.

Unfortunately, every new migration must be added to this method Seed()

in the future when the database is created from scratch.

And where did I get the sixth line used for the model? I copied it from SQL Server Management Studio from the migrated version of the database.