CODEWITHSUNDEEP
entity-frameworkentity-framework-migrationslocaldb
Dan
Dan

Reputation: 7744

How to start using EF code first migrations on an existing set of databases whilst also using LocalDB for testing

I am working on a system that currently has a number of environments (test, stage, live, etc) each with their own database. So far these databases have been kept in sync by running the same update scripts on each of them.

We are now moving to using EF6 code first migrations, and would also like to start writing some automated system tests using LocalDB.

I've found https://msdn.microsoft.com/pt-pt/data/dn579398 which describes two options for adding an initial migration.

The first method creates an empty initial migration which will work great for the existing environments but won't help with creating LocalDBs for testing.

The second method creates a migration to bring up the whole database from scratch (minus things EF doesn't care about such as sprocs and views). This would be acceptable for testing, but not good for actually recreating a databse. It also requires you to manually comment out the Up method, run the migration on all existing databases, and then put the Up method back. As it will take a while to get the migration through all the environments I'm not keen on this. It also violates the one of the principles of migrations which is that they shouldn't be edited once they've been released.

Having some kind of conditionality in migrations would solve my problem (e.g. if(tableExists("A_table_in_the_existing_database") return;) but there doesn't seem to be anything like that available.

The best I've come up with is to dump the existing database schema from SQL server to a file (which has the advantage of preserving sprocs, views, etc) and then use option 2 above, except instead of using the generated Up method I'll run the SQL file.

However, this still has the drawbacks of option 2 mentioned above, so I'd be very happy to learn of a better way of handling this situation.

Edit: Would this work? Run the commented out initial migration on one database, then dumping out the __MigrationHistory table and inserting it into the other databases? That way I wouldn't have to wait for the migration to make it through all the environments before I could safely uncomment it.

Upvotes: 1

Views: 254

Answers (1)

Dean Ward
Dean Ward

Reputation: 4793

EF 6.1.2 has support for running SQL embedded as a resource within the assembly containing the migrations using the SqlResource method.

I think I'd go with scripting out your existing schema and using an initial migration that executes SqlResource as its Up. If all it's doing is a bunch of IF EXISTS then it shouldn't take too long to run. Otherwise scripting out __MigrationHistory will also work if you just want to run it once locally and apply to all your other databases by hand.

Upvotes: 1

Related Questions