Peter Keating

Developer from the New Forest in the South of England.

Setting up Entity Framework Migrations


This weekend I've been adding a database to the Moov2 website and thought it would be useful to share the process of setting up database migrations via Entity Framework.

When working on a .NET project that relies on communication with a database it is important that the schema of the database is kept inline with the code in the project. This is exactly what database migrations are responsible for, managing the schema of the database so it correlates with the code in the project. Having the schema defined in code has huge benefits for collaboration and continuous deployment. Having the code base for a project with migrations means a database on a local or production server environment will be updated automatically with no effort required by a developer. This also removes any mistakes being made had someone had to manually run any SQL on a database to make changes.

Entity framework includes a straight forward approach to database migrations where each change to the database is represented by a class in the project. Each class contains an Up method to describe a change to the database, and a Down method that describes how to revert that change.

Lets get to setting up, first step is to add a reference to the latest version of Entity Framework (v6.1.2 at the time of writing) to the project via NuGET.

In order to run migratios with Entity Framework a DbContext is required to handle the connection to the database. Lets create a class that extends DbContext and passes the connection string into the constructor. Side note, the ConnectionStrings.Get is a custom static method that returns a connection string extracted from the Web.config.

using Moov2.Website.Core.Database;
using System.Data.Entity;

namespace Moov2.Website.Migrations
{
    public class MigrationsDbContext : DbContext
    {
        public MigrationsDbContext()
            : base(ConnectionStrings.Get())
        {

        }
    }
}

Our custom class MigrationsDbContext will be used in a class that extends DbMigrationsConfiguration and defines the configuration for the migrations.

using System.Data.Entity;
using System.Data.Entity.Migrations;

namespace Moov2.Website.Migrations
{
    internal sealed class MigrationsConfig : DbMigrationsConfiguration<MigrationsDbContext>
    {
        public MigrationsConfig()
        {
            AutomaticMigrationsEnabled = false;
        }
    }
}

The configuration class above is instructing which context should be used and is disabling automatic migrations. By disabling the automatic migrations we are taking responsibility of schema updates giving a better control of schema versioning and means downgrading is possible if required.

The migrations should run when the application starts. The Moov2 website is built using Nancy, which uses a bootstraper class to handle configuring a project when an application event happens, for example application start. The bootstrapper has an ApplicationStartup method which is where the migrations should be run. An instance of DbMigrator is created passing in an instance of our custom configuration, MigrationsConfig. All that is left to do is to call to the Update method to run any new migrations, as shown below.

public class Bootstrapper : DefaultNancyBootstrapper
{
    protected override void ApplicationStartup(TinyIoCContainer container, IPipelines pipelines)
    {
        base.ApplicationStartup(container, pipelines);

        RunMigrations();
    }

    /// <summary>
    /// Runs any migrations to update the schema of the database.
    /// </summary>
    private void RunMigrations()
    {
        var migrator = new DbMigrator(new MigrationsConfig());
        migrator.Update();
    }
}

With the runner setup, we can now move on to defining the schema changes. Commonly the first migration would contain table creation unless maybe you're starting with an existing database. To create a new migration you need to open the Package Manager Console (View -> Other Windows -> Package Manager Console). From within the console the command below will create a new migration named CreateDocumentsTable.

Add-Migration CreateDocumentsTable

Once the command has completed Visual Studio will present the class with the name used in the add migration command. CreateDocumentsTable contains an Up and Down method. As mentioned earlier, Up contains the database schema changes whereas the Down method handles reverting the schema change. The Add-Migration command has created the CreateDocumentsTable class in the Migrations package in a file whose name contains the date & time that the migration was created and the name of the migration.

The DbMigration class that CreateDocumentsTable migration class extends contains useful methods to help define schema changes such as AddColumn, CreateTable, CreateIndex, DropColumn, DropTable and many more. Also there is a method named Sql that accepts a string containing SQL, which means any SQL can be executed on the database in a migration. Below is the migration for creating a table named Documents.

public partial class CreateDocumentsTable : DbMigration
{
    public override void Up()
    {
        CreateTable("Documents",
            c => new
            {
                Id = c.Int(nullable: false, identity: true),
                Content = c.String(),
                ContentType = c.String(maxLength: 150)
            })
            .PrimaryKey(t => t.Id)
            .Index(t => t.Id);
    }

    public override void Down()
    {
        DropTable("Documents");
    }
}

The table consists of three columns, Id is an auto increment primary key column whereas the other two columns are strings. One thing to note, if you don't specify a maxLength (e.g. the Content field) then the column is created with a type of nvarchar(max), this is new to Entity Framework version 6. Below is a screenshot of the database that is created after running the migration.

The __MigrationHistory table is automatically created and contains a record of which migrations have been run.

That's database migrations setup and ready to go with the first migration executed. Any further schema changes require a new migration to be added using the Add-Migration command and then defining the schema changes in the generated class.

All the source code from this blog post can be found in the source code repository for the Moov2 website on Github.

Back to Posts

-->