Migrate database-first to code-first - switch from

Question

We need to migrate an existing project from database-first to code-first. Each environment (DEV, TEST, PROD) has a slightly different version of the database. The differences are the changes in DEV that may not have been pushed to TEST and PROD.

--

We are using Entity Framework 6.1.3.

We have a database project that deploys the database. The publish operation compares the database with the project and builds a script to change the database as needed. We have a models project with an edmx diagram. We update the edmx from the database and this generates the entities based on the tables in the database.

We are replacing the models project (with the edmx) with a code-first project. The database project will go away and our new code-first project will have migrations.

--

We used EF PowerTools to reverse engineer the database and create the models. Then we create a migration and it adds the statements needed to create the database. However, when we update-database it crashes because the tables already exist. Next, we created a migration that ignores the changes. Now update-database does nothing. This is fine because everything already exists. But what happens if I need to deploy to a new empty database? It will not create any of these objects.

To complicate matters, some of the objects pulled in from the reverse engineer in DEV are not in TEST. And some tables in TEST may have changed in DEV. And then PROD may also be different.

--

How can we transition from database-first to code-first and still have a reliable process that works in all environments and can build a stand-alone database?

Answer 1

I would start with your lowest baseline, probably PROD. Attach it to your project and generate the migration "Initial" or whatever. Comment out the Up() code and update-database. Attach the second most recent database, probably TEST. Do the update-database again (Initial). Add a second migration "TestChanges". Comment out the Up() code and update-database. Attach the DEV database. Apply the first migration (Update-Database –TargetMigration: Initial), apply the second. Now add-migration "DevChanges", comment out the up code, update-database. Now you can either uncomment the migration code or generate a script to get from any of those points:

Update-Database -Script -SourceMigration: Initial -TargetMigration: DevChanges

I'd probably call the migrations something more meaningful though.

https://msdn.microsoft.com/en-us/data/jj591621#specific