Using Entity Framework 4.3 Database migration for any project

In this blog post I’m going to write about the Entity Framework 4.3 Database migration and how to use it without using Code-First or Entity Framework as an OR-M at all.

To get started just create a simple project, for example a Console Application. After that write the following in the Package Manager Console (In the Visual Studio menu, select View/Other Windows/Package Manager Console). Write the following and hit enter:

PM> Install-Package EntityFramework

After the EntityFramework 4.3 is installed we need to enable the database migration. Only one project in our solution can be enabled. To enable migration just enter the following in the Package Manager Console:

PM> Enable-Migrations

When this is done we will now have a folder in our project called Migrations. In this folder we will see Configuration.cs file (this file may showed up after you have enabled the migrations).

In the Migrations folder we will add our database changes for the current version of our code. More about this later.

Open the application configuration file and add a connection string:

<?xml version="1.0" encoding="utf-8"?>

<configuration>

  <configSections>

    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->

    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=4.3.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

  </configSections>

  <connectionStrings>

    <add name="BlogContext"

         providerName="System.Data.SqlClient"

         connectionString="Data Source=.;Initial Catalog=MyBlog;Persist Security Info=True;Integrated Security=true"/>

  </connectionStrings>

</configuration>

I gave the connection string the name “BlogContext”, I also use a database with the name MyBlog.

If you use Entity Framework and code-first, Code-First can create a database for us based on our model. But this blog post will be about not using Entity Framework as the OR-M at all, instead I already have an existing database called “MyBlog”. This database has one table called “Blog” with two column, BlogId and Name:

Note: The _MigrationHistory table as you can see in the image above is a table that will be created by EF 4.3 Database Migration to keep track of the migrations. It will use that table know the current state of the migration, only to make sure it knows which migrations that is in pending state (has not bean applied) etc.

This blog post pretends that we already have an earlier version of our program that works against the database schema above. Now in our new version we are going to add one new column for specifying when a Blog record is created.

After our connection string is in place, we need to create a DbContext class. The EF 4.3 Database Migration needs a DbContext, we will just create an empty DbContext. By doing so we can use the EF 4.3 Database migration without using EF as our OR-M.

Add a new class with the same name as the connection string, for example “BlogContext”, we must make sure it will inherit from the DbContext class:

namespace ConsoleApplication6.Model

{

    using System.Data.Entity;

    public class BlogContext : DbContext

    {

    }

}

Note: EF will by default try to locate a connection string with the same name as our DbContext class.

When this is done we need to open the Configuration.cs file located in the earlier created Migrations folder. We need to specify that the configuration of the migration should use our BlogContext, we do that by using the DbMigrationsConfiguration<T> and specify that the T is our BlogContext. The configuration class is used to configure the database migration, it also need to know which database it should work against etc. This is done by specifying a DbContext and in our case our BlogContext:

namespace ConsoleApplication6.Migrations

{

    using System.Data.Entity.Migrations;

    using ConsoleApplication6.Model;

    internal sealed class Configuration : DbMigrationsConfiguration<BlogContext>

    {

        public Configuration()

        {

            AutomaticMigrationsEnabled = false;

        }

    }

}

Note: The AutomaticMigrationsEnabled is set to false by default, if we use Code-First and want to let the migration tool automatically handle migrations, we can enable it and just add the changes to our Model, and the migration will check for the changes and do migration based on it, here is more information about it: EF 4.3 Automatic Migration. But in this example we will handle the migration manually.

There is Seed( xxx ) method added to the Configuration file, by using that method we can for example use it to fill/seed our database with data after a migration. I have removed that method because in this example we will not use it.

Now when all the basic configuration of our Migration is setup, we can start adding a migration. We do that by using the command “Add-Migration”. In this example we will add a new column to our Blog table called “Created”. So in the Package Console Manager we write:

PM> Add-Migration AddBlogCreated

We will now have a file added to the Migrations folder with the name “AddBlogCreated.cs” and the file will also have a timestamp as a prefix, this is used to know the order the migration should taka place. A Migration file will have two methods, Up and Down. The Up method will be used to specify what changes the current version of our application need to apply to the database, the Down is used to reverse the changes we have added to the Up method. When EF 4.3 Database Migration will update the database, it will run all migration in the timestamp order, and only those that haven’t been used since the last update (The _MigrationHistory table know about which migration that was last used). The Up method of all migrations will be called and do the changes we have specified to the database. If we decide to go back to a previous migration, the Down method will be called to redo the changes in a revers order.

By using the AddColumn method in the migration file, we can easy add columns to our database, by using the RemoveColumn, we can remove a column that is added. The following example adds a column to the “Blog” table with the name “Created” and specify that it’s a DateTime, it will also remove the column if the migration is reversed.

namespace ConsoleApplication6.Migrations

{

    using System.Data.Entity.Migrations;

    public partial class AddBlogCreated : DbMigration

    {

        public override void Up()

        {

            AddColumn("Blog", "Created", c => c.DateTime());

        }

        public override void Down()

        {

            DropColumn("Blog", "Created");

        }

    }

}

When specifying the column type we can also specify if the column is nullable or has a default value etc.

AddColumn("Blog", "Created", c => c.DateTime(nullable:false, defaultValueSql:"GetDate()"));

We can also use pure SQL in the migration class by using the Sql method:

public override void Up()

{

   AddColumn("Posts", "Abstract", c => c.String());

   Sql("UPDATE Posts SET Abstract = LEFT(Content, 100) WHERE Abstract IS NULL");

}

Other method that can be used are for example: RenameTable, RenameColumn, CreateTable add and remove index etc.

Now when we have added our AddBlogCreted migration to just simply add a new column, we want to run our migration. We can do it by using the Update-Database command in the Package Manager Console:

PM> Update-Database

The Update-Database will now execute all migrations that is added and not “executed” since last update. The timestamp prefix of the migration files as mentioned earlier is used to execute the migration in a correct order. If we run this command we will now have a new column called “Created” in our Blog table.

If we also want to see the SQL of the migration we can add the –Verbose parameter after the Update-Database:

PM> Update-Database -Verbose

To go to a specific migration, we can use the Update-Database and use the –TargetMigration parameter, for example:

PM> Update-Database –TargetMigration:"AddBlogCreated"

This will take us to the “AddBlogCreated” migration state. At the moment we are at this state so nothing will happen. We can try it by going back to a previous state, because we only have one migration we need to back to the first state of the migration (initial state), this is done by set the –TargetMigration value to “$InitialDatabase”:

PM> Update-Database –TargetMigration:$InitialDatabase

When we run the command above, we can see in the Package Manager Console that our AddBlogCreated migration is reverted. If we look at our database we can see that our Created column is removed. Now if run the Update-Database command and sets the –TargetMigration to “AddBlogCreated”, we now moves to the “AddBlogCreated” migration state and our Created column is now added.

We can also execute the Update-Database command and get SQL script and pending migrations etc from code. This can be done by using the DbMigrator class and the MigratorScriptingDecorator etc. Here is an example where a migration is updated and the SQL script is displayed in a Console application:

class Program

{

   static void Main(string[] args)

   {

      var configuration = new Configuration();

      var migrator = new DbMigrator(configuration);

      var scriptor = new MigratorScriptingDecorator(migrator);

      var script = scriptor.ScriptUpdate(sourceMigration: null, targetMigration: null);

      Console.WriteLine(script);

      migrator.Update();

      var pending = migrator.GetPendingMigrations();

      Console.ReadLine();

   }

}

Summary

In this blog post we have seen how we can use the new Entity Framework 4.3 Database Migration and use it without even use Entity Framework as our OR-M. We have seen how to install and configure the migration, also how to apply a migration and reverse it.

Ps. If you want to know when I post another blog post, please follow me on twitter @fredrikn