Dan Nolan

6 August 2015

Deploying Entity Framework code first migrations with T-SQL

If you’ve reached the point where the DB change management approach of Code First migrations is no longer meeting your deployment needs, it might be time to consider moving to a change script-based approach.

This week’s ReadyRoll release introduced a simple integration with Code First that allows you to continue modelling your database using Entity Framework, but to leave the actual deployment of your database to ReadyRoll using T-SQL based migrations.

Why would you want to do this?

Entity Framework offers a great workflow for modelling and deploying database changes when working locally. Schema changes can be developed very quickly and with little effort, however things can get difficult when trying to use the tool in a team environment, or if you want to try and orchestrate other changes with your database deployment, such as updating data or performing more complex schema alterations than can be supported by Code First migrations.

ReadyRoll database projects follow a simple T-SQL change script approach, where each script is executed against a given target database a single time in a pre-defined numerical order. So predicting the outcome of a deployment is as simple as viewing the list of scripts in a folder. Its integration with Visual Studio nicely positions it alongside your other solution assets, providing a simple “one click” build experience for all components of your application.

How does it work?

We’re going to start with Microsoft’s own sample project for Entity Framework Code First, Contoso University, to work through an example of how to adapt an existing database to a T-SQL migrations based deployment solution.

After installing ReadyRoll and opening the sample solution in Visual Studio, click File… New Project, and within the Templates… Other Languages… SQL Server node, select ReadyRoll SQL Server Database Project.

Give the project the name ContosoUniversity.Db and from the solution drop-down, select Add to solution, then click OK. This will create an empty ReadyRoll database project, and also automatically display the ReadyRoll DBSync tool-window showing the new, empty database that was created on your local SQL Server instance.

We want to point our new project to the same database as the Entity Framework-enabled project, so clickConfigure to set the connection properties.

Set the Server name to (localdb)\ProjectsV12 if you’re using Visual Studio 2013/2015, and the database name to “ContosoUniversity” (Please note: older versions of Visual Studio use (localdb)\Projects).

Within the ASP.NET MVC project, adjust the SchoolContext connection string property within theWeb.config file to also use this server and database name.

Now lets generate the baseline for the project. The baseline is a script that is generated from the current state of the schema model that sets the starting point for all future migrations. The baseline you create in this step should reflect the state of the schema model in your Production environment.

Create the baseline script by opening the Package Manager Console window and executing the following:

This causes Entity Framework to generate the T-SQL logic for all existing migrations, and for the script to be automatically added to your new ReadyRoll database project.

The $InitialDatabase argument causes Entity Framework to generate a script that should be runnable on all target environments, regardless of whether a new database is being created (as in this case), or if a subset or even all migrations have already been deployed to an upstream environment such as Test orProduction.

The widget in the top-right hand corner of the document window displays the current deployment status of the migration.

Click Deploy Project to execute this script against your local SQL Server instance.

Now that the baseline script has been created, let’s hand over control of deploying the database to ReadyRoll.

To do this, Automatic Migrations will need to be enabled, but don’t worry. We’ll still be generating migrations, it’s just that they’ll be in T-SQL format, rather than in Fluent (i.e. C# migration class) format. Within the Migrations project sub-folder, edit Configuration.cs.

Set AutomaticMigrationsEnabled=True and save.

Authoring a model change – How to go Up

Let’s try making a change to the database model. From the Models sub-folder, open the Person.cs class file. Change the length of the LastName and FirstName properties to 255.

Switch to the Package Manager Console window and execute the following:

This will generate a new script that, unlike the previous script, assumes a certain state is already present in the target database. So it will only contain the most recent change that was made to the model.

Before deploying the script, you have an opportunity to make any changes to the contained logic. This may be needed, for example, in the scenario where a column is being renamed, but Entity Framework scripted it as a DROP COLUMN / ADD COLUMN set of statements.

In this case, the schema change is very straightforward, so let’s go ahead and deploy the migration to the database.

To confirm that everything went as planned in the deployment, you can use the DBSync tool-window that ships with ReadyRoll.

From the View menu, select ReadyRoll DBSync.

Click View Pending Changes to start the schema comparison process. This will build a separate (empty) copy of your schema called the Shadow database, based purely on the migrations present in your project. This is compared back to the Sandbox database that this project was connected to in the first step.

The DBSync tool should show that the two databases are in-sync. You can explore the objects within the schema by expanding the Identical group and double-clicking an item to view its definition.

Reverting a model change – How to go Down

Say you decide at this point that you don’t actually want to go ahead with changing the length of the column, and you want to get rid of that last migration you just applied. If we just delete the migration without doing anything else, then we’ll end up with drift in the sandbox database.

Drift, in this case, would occur as a result of modifying the baseline of an already-deployed database. Now because we haven’t shared this change with any of our fellow team members yet, it’s still safe to delete that migration but we will need to correct the drift in our sandbox database before proceeding.

Firstly delete the migration from the project (or alternatively, discard the new migration and project file changes using your source control tool). Then, click the Refresh button in the DBSync tool, to perform a new sandbox vs shadow database comparison. This will rebuild the shadow database to reflect the revised project model.

Note that a pending change is displayed for the [Person] object. Double-click the item to display the pending change to display the side-by-side diff window.

On the left hand side of the comparison, you’ll see the shadow version of the object, i.e. the version of the object as represented by the project’s migration scripts. On the right hand side, you’ll see the sandbox version, i.e. the version that ReadyRoll wants to import into the project. Except in this case, we don’t want to import this change, we want to discard it from the sandbox schema.

To discard the change, right-click the change and click Revert Selected Object(s).

This will undo the column length change, thus bringing the database into synchronization with the project sources.

There’s one last step: we need to make sure that Entity Framework remains in-sync with our connected database. We’ll do this by tracking the data within the [__MigrationHistory] table.

Still within the DBSync tool-window, scroll down to the [__MigrationHistory] table, right click the item and select Include Table Data.

At this point, you’ll be prompted to refresh the DBSync tool. Click Refresh (View Data), and the list of pending changes should update.

As with the schema change before, bring Entity Framework into synchronization with the ReadyRoll project by right-clicking the item and selecting Revert Selected Object(s).

Alternative approach – Author with Fluent and distill to T-SQL

At the beginning of this tutorial, we set Automatic Migrations=True in the Entity Framework-enabled application in order to bypass Fluent in favour of generating just T-SQL migrations. This suits teams that want to shift entirely over to T-SQL based migrations for your deployment, but what if you’d like to follow a hybrid approach: continue to use Fluent to author your changes, but use T-SQL to deploy to your Test andProduction environments?

The good news is that, if you prefer, you can leave AutomaticMigrations=False and continue adding Fluent migrations to your application project. You can then use the Update-Database -Script command to subsequently add the T-SQL scripts into your ReadyRoll project. This may benefit teams that may choose to delay the generation of their T-SQL scripts until certain checkpoints in a project lifecycle are reached (e.g. just before the Test environment deployment).

What to do next?

Now that you have a baseline set for your project, you’ve linked the application and database components of your solution into a singular development workflow, you can begin exploring how you might like to exploit having the ability to control all aspects of your deployment within the Visual Studio IDE.

Some next steps you might like to try:

• Importing static data into the project. You may like to include the deployment of country lists, product types or other types of data that is controlled by your development organisation (ReadyRoll can script the insert/update/delete statements for you).
• Switching on Offline Schema Model. To complement your application’s data model, you can enable within your ReadyRoll project settings a view of the physical database model to give you a complete picture of your system’s assets.
• Enabling your database project for continuous integration/deployment. If your CI server is already configured to build your solution, then your database project should already be being built. Take it to the next level by taking advantage of ReadyRoll’s strong integration with Octopus for an end-to-end solution.