Entity Framework code first migrations are really handy, but can be dangerous in the wrong hands as they make changes to the underlying database of your application.

Before the days when a single check in would run through the build process CI/CD pipeline, it was normal for the development stage to make constant changes to a database structure as an when the features requested needed it. With the new model of all changes running through the CI/CD pipeline, the database changes could be quickly pushed to a test or staging server in no time at all. With this new model of writing code, a few precautions have to be taken to make sure EF migrations don’t wreck havoc on the database running the staging or test servers.

Migrations in Entity Framework

A migration is a change to the schema of a database based on any model changes that have been created in the data layer of the application. If for example there is a User class and a new property is added for membership level, then with migrations enabled, a new column will be created in the database to match the new property.

But for a database already in existence and maybe even already in production, you don’t want changes to the schema to go directly through to production. So there are a few steps to take, these are:-

  1. Create a copy of the existing database on the local machine
  2. Reverse engineer the database to classes that can be used for Code First migrations
  3. Enable migrations
  4. Create a manual empty migration to tell EF that both model and database are in sync
  5. Disable migrations for all but local changes
  6. Generate a migration script for the dba to implement

Create a copy of the existing database on the local machine

I will leave this step up to you as some developers prefer to either restore a database back up locally, script the entire database and data and run the script locally or you may even have a database on another server that you can use.

Reverse engineer the database to classes that can be used for Code First migrations

By using the Entity Framework Reverse POCO Generator Visual Studio plugin, it is possible to point it at the database and generate the DbContext and all the necessary POCO classes.

Right click the project in Visual Studio and choose Add > New item.. from the context menu, you will then get a choice that includes ‘EntityFramework Reverse POCO Code First Generator’ in the template list.

reverse-engineer-db

By default this will create a context with the name MyDbContext like this:-

image

Simply change the ConnectionStringName to match that in your app.config or web.config and save the file and it will generate the necessary classes in a cs file under the T4 template.

image

 

As this tool uses a t4 template to generate the classes, it is easily configurable for example to change the context class name.

 

Enable migrations

To enable migrations, simply go to the ‘Package Manager Console’ view in Visual Studio. It can be found either by typing in the Quick launch text box at the top right or View > Other Windows  > Package Manager Console. In the default project drop down make sure the correct project is selected then type:-

Enable-Migrations

This will create a ‘Migrations’ directory within the project which will have a Configuration.cs file inside.

This file will house the main configuration class for the migrations and it is in here where you can change whether AutomaticMigrations are enabled and if data loss is acceptable. On a development machine, that is probably OK, but you don’t want those settings passed into staging or production; so use a pre-processor directive such as this

In this class, you will notice the DbMigrationsConfiguration is being inherited from which is using the DbContext class we created when we reverse engineered the database. It is this class that is responsible for the model and to keep track of the migrations.

Going back to the generated context class we need to add references to System.Data.Entity to get access to the functionality to drop and recreate the database and pluralize names. This can be added to the OnModelCreating method like this:-

By setting the Initializer context to null stops any changes being made to the schema, whereas setting it to DropCreateDatabaseIfModelChanges will do just that.

Create a manual empty migration to tell EF that both model and database are in sync

Now you have the model that matches the database schema, you can manually add a migration using this command:-

Add-Migration –IgnoreChanges

image

By giving it a name it will be easier to identify it later on in the migrations collection and will also allow it to generate a class with the Up and Down methods that are used to generate logic to change the schema to different versions. As this is the initial synced migration, these should be left empty.

image

Disable migrations for all but local changes

Now that the switches are put in place, when the code goes through the CI/CD pipeline just build it with a switch of anything but Debug. For example in TeamCity I have a build step using MSBuild using the parameter /p:Configuration=Release

 

Generate a migration script for the dba to implement

Lets face it, nobody wants to feel the wrath of a DBA if you make changes to the production database without firstly notifying them. There should even be a proper change management process that would need to be followed through before any changes are pushed to production. This is where the script generation feature from Entity Framework comes in handy.

By using the command:-

Update-Database –Script

You can generate a TSQL script that will show all the changes needed to get the database up to data with the model; this script can then be run by the DBA on the staging or production servers. Here I have added a simple class called TestTable in the generated cs file.

 

 

image

Happy coding.