EntityFramework 6 Migrations for both existing and new databases?

c# entity-framework entity-framework-6 entityspaces migration

Question

We have a customer base with pre-existing databases for our product. EntitySpaces is currently used to access the databases, but because EntitySpaces is no longer supported, we'd like to move to EntityFramework (v6). Additionally, we want to leverage the migrations capability. Since we only want to allow database migration to an admin user, automatic migrations are blocked.

The EF model was created using an existing database. Everything functions very well, however the big challenge we face is identifying new or empty databases from existing databases that match the model but have not yet been migrated to EF (due to the absence of the MigrationsHistory table). An empty migration works great for converting already-existing databases, but we also require a migration that contains the entire model for newly-created databases. There will always be conflicts with existing databases when the migration chain includes an initial migration. Of course, we may develop a workaround by creating and populating the MigrationsHistory table using external SQL scripts or ADO commands. But since some of our clients use Oracle databases and some use MsSql databases, we'd like to avoid that. Therefore, we would really like to preserve the abstraction layer that EF provides.

Is it possible to use code-based migrations to have EF handle both new and current databases without resorting to non-EF workarounds?

1
2
11/7/2013 9:07:31 AM

Accepted Answer

My initial recommendation was to catch the CreateTable exception, but it turns out that this is done in a different location and cannot be caught within the exception.

If the Seed technique is not already existent, starting from scratch with a new database will be the most straightforward course of action. To carry out...

  1. Add an Initial Create migration to a new, empty database, then take the resultant SQL.

    Add-Migration InitialCreate
    Update-Database -Script
    
  2. Note down this script. If you absolutely want to, you could leave it inline in your code or add it to a resource or static file.

  3. Remove all of the migration's code, leaving only the Up() and Down() functions in place. This will enable you to execute your empty migration and result in the creation of the MigrationHistory table.

  4. You can dynamically execute SQL in your Migration configuration class by executing queries usingcontext.Database.SqlQuery and context.Database.ExecuteSqlCommand . Check to see if your major tables are present; if not, run the script that was prepared above.

Although it isn't very tidy, this is easy to put into practice. Test it thoroughly because the Seed function executes after EVERY migration, not just the first. Because of this, you must first check to see if a primary table exists before taking any action.

Writing a "CreateTableIfNotExists" function for migrations would be the more challenging way, but doing so would require using Reflection to invoke internal methods in the DbMigration class.

6
11/8/2013 12:19:28 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow