2

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?

hoekki
  • 228
  • 1
  • 11
  • A bit off topic, but FluentMigrator's Schema.Exists expressions seem to be all that is needed to support this use case. I find it frustrating that EF doesn't appear to have this functionality. – Ellery Newcomer Oct 29 '14 at 17:53

1 Answers1

6

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.

Richard
  • 29,854
  • 11
  • 77
  • 120
  • Thanks for your quick response, Richard. Tried your approach, as it looked exactly like what I was looking for. The problem is, the actual execution of the SQL happens outside the up/down methods somewhere else inside EF. So, catching SqlExceptions inside the migration code won't help. Even tried to execute a bogus SQL command via Sql(...) method. So the Exception has to be caught outside the migration, around the Update(...) method. But there you don't have direct access to the database. Back to the start. – hoekki Nov 07 '13 at 13:19
  • Hmm, hadn't realised this. Looking at the source, you should be able to create a custom migration operation, but this isn't going to be easy. The internals of the migration process are all "internal", but you may be able to use Reflection to create a "CreateTableIfNotExists" method. I can't help much with the how part of that though! – Richard Nov 08 '13 at 12:04
  • Updated answer with a new suggestion. – Richard Nov 08 '13 at 12:19
  • Thanks for the update. Although your suggestion would have worked eventually, I finally decided to fall back to simple sql scripts. Though I need a separate script for each and every dbms, still less pain in the a... I'm going to suggest some kind of pre- and post migration hook to the EF team. Cheers mate. – hoekki Nov 12 '13 at 08:30