EntityFramework 6 Migrations for both existing and new databases?

Question

In our software, we have a customer base with existing databases. The databases are currently accessed via EntitySpaces, but we'd like to switch to EntityFramework (v6), as EntitySpaces is no longer supported. We'd also like to make use of the migrations feature. Automatic migrations are disabled, since we only want to allow database migration to an admin user.

We generated the EF model from an existing database. It all works pretty well, but the real problem we have, is, programmatically distinguishing between existing databases that match the model but have not yet been converted to EF (missing MigrationsHistory table), and empty/new databases. Converting existing databases works well with an empty migration, but for new databases we also need a migration containing the full model. Having an initial migration in the migration chain always clashes with existing databases. Of course we could create a workaround with external SQL scripts or ADO commands, creating and populating the MigrationsHistory table. But that is something we'd like to avoid, because some of our clients use MsSql databases, some use Oracle. So we'd really like to keep the abstraction layer provided by EF.

Is there a way to get EF to handle both existing, and new databases through code-based migrations, without falling back to non-EF workarounds?

Answer 1

My original suggestion was to trap the exception raised by CreateTable, but it turns out this is executed in a different place so this cannot be trapped within the exception.

The simplest method of proceeding will be to use the Seed method to create your initial database if it is not present. To do this...

1. Starting from a blank database, add an Initial Create migration and grab the generated SQL

   Add-Migration InitialCreate
   Update-Database -Script
   

2. Save this script. You could add it to a resource, static file or even leave it inline in your code if you really want, it's up to you.

3. Delete all of the code from the InitialCreate migration (leaving it with a blank Up() and Down() function). This will allow your empty migration to be run, causing the MigrationHistory table to be generated.

4. In your Migration configuration class, you can query and execute SQL dynamically using context.Database.SqlQuery and context.Database.ExecuteSqlCommand. Test for the existence of your main tables, and if it's not present, execute the script generated above.

This isn't very neat, but it's simple to implement. Test it well, as the Seed method runs after EVERY migration runs, not just the initial one. This is why you need to test for the existence of a main table before you do anything.

The more complicated approach would be to write a "CreateTableIfNotExists" method for migrations, but this will involve use of Reflection to call internal methods in the DbMigration class.