If you are using Code first approach for developing a production scale application you would have to use migrations concept of entity framework to keep your database up to date based on the changes to your classes or model.Migrations provides you with a step by step approach to keep your database up to date based on your changes and also you can keep these changes in version control so that anytime you can go back to them without having to keep your whole database in version control.

Lets see how this works…

Pre-requisites

For the scope of this blog post I am not going to get into details of how to model classes for code first approach.I am assuming you are aware of basics of entity framework and code first approach(If not you can go to this plursalsight course on Entity Framework and Data Models by Julia Lerman)

Below are quick steps for setting up your code first project.

• Create a  project which is having your model or POCO classes .For this blog post I will be using a class library project with below entities.

• Install and add reference to entity framework using package manager console.

• Create your context class.For this blog post we have below EmployeeContext Class.

• Appropriate entries in web.config or app.config file.In my case I am using a console application and below is the entry in the App.config file for database.

<entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="Server=JAGS\SQL2012; Integrated Security=True;" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>

I am using a Sql Express 2012 database.In case you don’t have SqlExpress you can use the default configuration with local db.

When you run the application and first time access any of the entities , entity framework will create the database for you.In my case i have below code in my console app main method.

class Program
    {
        static void Main(string[] args)
        {
            using(var context = new EmployeeContext())
            {
                var employees = context.Employees.ToList();
                employees.ForEach(x => System.Console.WriteLine(x.FirstName + "," + x.LastName));
            }
        }
    }

when var employees = context.Employees.ToList(); line executes entity framework will create database based on our model.

From now onwards whole story is about what happens when you change your model and run your application again…

Recommended Books on Amazon :

[easyazon_link identifier=”1449312942″ locale=”US” tag=”codicanv-20″]Programming Entity Framework: Code First[/easyazon_link]

[easyazon_link identifier=”1430257881″ locale=”US” tag=”codicanv-20″]Entity Framework 6 Recipes[/easyazon_link]

[easyazon_link identifier=”1512003115″ locale=”US” tag=”codicanv-20″]Entity Framework Code First :The Ultimate Training
Guide[/easyazon_link]

Enabling Migrations

In code first whenever your classes change you have couple of options on how database should be affected by those changes. These are called database initialization strategies.

1. CreateDatabaseIfNotExists : Create database if does not exist.This is enabled by default.
2. DropCreateDatabaseIfModelChanges: Drop and create database  if your domain model changes
3. DropCreateDatabaseAlways: Always drop and create database irrespective of your domain model.
4. Custom DB Initializer: Create your own DB initializer i.e. custom implementation on what should happen when the domain model changes.

Entity framework 4.3 introduced another option called MigrateDatabaseToLatestVersion which is what we are focusing in this post.

Basically there are two kind of migrations available :

1. Automated Migrations: This is the option which was introduced in EF 4.3 which allowed database to be updated automatically with changes done to model whenever you run the application.
2. Code Based Migration : This option allows more control on how the migration is done by providing a code file listing what steps would be done in migration which can be customized if required.

For enabling migration run below command in package manager console.

> Enable-Migrations

This will create below class in Migrations folder of your project.

internal sealed class Configuration : DbMigrationsConfiguration<CodeFirstLib.EmployeeContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = false;
        }

        protected override void Seed(CodeFirstLib.EmployeeContext context)
        {
            //  This method will be called after migrating to the latest version.

            //  You can use the DbSet<T>.AddOrUpdate() helper extension method
            //  to avoid creating duplicate seed data. E.g.
            //
            //    context.People.AddOrUpdate(
            //      p => p.FullName,
            //      new Person { FullName = "Andrew Peters" },
            //      new Person { FullName = "Brice Lambson" },
            //      new Person { FullName = "Rowan Miller" }
            //    );
            //
        }
    }

This by default will have Automatic migrations disabled (AutomaticMigrationsEnabled = False  ).You can have automatic migrations enabled by setting this property to true or directly running the Enable-Migrations command with flag ‘-EnableAutomaticMigrations’.

Once the Configuration class is created we need to add below code to set MigrateDatabaseToLatestVersion as database initializer.

public class EmployeeContext : DbContext
    {
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            Database.SetInitializer<EmployeeContext>(new MigrateDatabaseToLatestVersion<EmployeeContext,Configuration>());
            base.OnModelCreating(modelBuilder);
        }

        public DbSet<Employee> Employees { get; set; }
        public DbSet<Department> Departments { get; set; }
        public DbSet<Function> Functions { get; set; }
        public DbSet<Qualification> Qualifications { get; set; }
    }

I have added the code to overridden OnModelCreating  method but this can be added to any other appropriate places as well.

Migration in Action…

Lets change something in model.I will add one more property in department class called DepartmentCode.

public class Department
    {

        public int  DepartmentId { get; set; }
        public string DepartmentCode { get; set; }
        public int DepartmentName { get; set; }

    }

Now at this point if I have automatic migrations enabled, when you run the application database will be updated with this change automatically.

Below is the updated table after running the application.

Again lets change the model by making Department Code as integer and this time we will make automatic migrations disabled.

Lets run the application again…

We get an exception called AutomaticMigrationsDisabledException. Below are the details in Additional Information section.

Additional information: Unable to update database to match the current model because there are pending changes and automatic migration is disabled. Either write the pending model changes to a code-based migration or enable automatic migration. Set DbMigrationsConfiguration.AutomaticMigrationsEnabled to true to enable automatic migration.

We will use the first option as suggested in above message i.e. code-based migration.

Code-Based Migration

For using code based migration we need to first run Add-Migration command in package manager console as shown below.

This will create a new timestamp based file in Migrations folder.

Below are the contents.

 public partial class DepartmentCodeTypeChange : DbMigration
 {
 public override void Up()
 {
 AlterColumn("dbo.Departments", "DepartmentCode", c => c.Int(nullable: false));
 }

 public override void Down()
 {
 AlterColumn("dbo.Departments", "DepartmentCode", c => c.String());
 }
 }

Up  method is used for upgrading the database whereas Down method is used to downgrade or rollback the changes.

Next step is to apply these changes to the database.For that we will be running  Update-Database command in console.

I am using –Verbose flag so that all the steps are listed in the console.Another useful option which you can use is –Script  flag which will generate the db scripts which can be run manually.

Lets do one more migration.In this case I have removed property MiddleName  from Employee  class.I’ll again run add migration command this will create another DbMigration class file.

This time lets generate scripts rather that updating the database directly.

In complex projects code-based migrations are usually preferred as it gives you  opportunity to review your migration and add some fine tuning wherever possible.

There will be few cases where Migration cannot handle the changes and you may have to tinker with code files to generate proper migration scripts.Also it is good to use this option when you have multiple databases which needs to be updated separately i.e. there are multiple versions or rollbacks to support.

For further study on this topic you go to this excellent pluralsight course on code first migrations by julia lerman.