Entity Framework: Database Migrations

If you don’t like writing SQL DDL statement, like me, than Entity Framework is very handy. With the Code-First, you can write your model and don’t need to worry about your database. It is generated for you by Entity Framework. This is very handy but what if you are working with an existing database? Or if you need to support different versions of your database? That is where database migrations come in to play.

Before we start

1. Create an Asp.Net MVC 4 application
2. Use Nuget to install the latest version of the Entity Framework (5.0.0-rc)

Because i am not running an SQL Express or SQL Server on my machine, i am also going to install SqlServerCompact so i can work with an SDF file. This needs a little more configuration.

1. Install EntityFramework.SqlServerCompact form Nuget.
2. Change the connectionstring in the web.config
     <connectionStrings>
       <add name="DefaultConnection" connectionString="Data Source=|DataDirectory|\DBTest.sdf;Max Database Size=2047" providerName="System.Data.SqlServerCe.4.0" />   
     </connectionStrings>

Create your models. In my case i will create as Car and Make class.

namespace EF_DatabaseMigrations.Models
{
    public class Car
    {
        public Int32 Id { get; set; }

        public String Model { get; set; }

        public Make Make { get; set; }
    }

    public class Make
    {
        public Int32 Id { get; set; }

        public String Name { get; set; }
    }
}

Next i will create a DbDatasource that will map my entities to the database. This datasource will pass the name of the connectionstring to the base constructor.

namespace EF_DatabaseMigrations.DB
{
    public class DbDatasource : DbContext
    {
        public DbDatasource()
            : base("DefaultConnection")
        { }

        public DbSet<Car> Cars { get; set; }

        public DbSet<Make> Makes { get; set; }
    }
}

Let’s migrate

To configure your application to use migrations, you have to type “enable-migrations” command in the Package Manager Console. This will create a Migrations folder that contains a Configuration file. This class contains a seed method that accepts the DbDatasource as a parameter. The seed method will always be execute when you update your database. This method will typically be used to create users in the database or in our case to create the Makes in our database.  By default, AutomaticMigrationsEnabled will be set to false. Since we want to automate everything, we need to set it to true :-).

namespace EF_DatabaseMigrations.Migrations
{
    internal sealed class Configuration : DbMigrationsConfiguration<EF_DatabaseMigrations.DB.DbDatasource>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
        }

        protected override void Seed(EF_DatabaseMigrations.DB.DbDatasource context)
        {
            context.Makes.AddOrUpdate(m => m.Name, new Make { Name = "Ford" }
                                                 , new Make { Name = "Opel" }
                                                 , new Make { Name = "BMW" });
        }
    }
}

Because the Seed method is always called, we have to make sure that the Makes we are adding, only are getting inserted if they don’t exists or are updated when they do exists the database. That is why we are using the AddOrUpdate methode. The first parameter is in fact our condition for the insert or update. If a make with a name “Ford” doesn’t exists in the database, it is inserted. If it does exists, it gets updated.

 

When we type “update-database” command in the Package Manager Console, EF will create our database and will call the Seed method. The location of the database will be determined by the connectionstring that you specify. In our case it will be found in the App_Data folder.

When we open the database, we see that a Cars and a Makes table are created. The Makes table is pre-filled.

 

In the table “_MigrationHistory” all migrations that where performed on this database are stored. That way you can always see what version of database this is. Also Entity Framework will need this information.

Now let’s assume that we want to add a BuildDate property to the Car. We can put this in a new migration. This way we can keep track of all our modifications in our database.

Change the Car entity.

public class Car
{
    public Int32 Id { get; set; }

    public String Model { get; set; }

    public Make Make { get; set; }

    public DateTime BuildDate { get; set; }
}

Add Migration

Type “add-migration AddBuildDate” in the Package Manager Console. This will create a new file in the Migration folder. The file name will contain the date + the name that we specified.

This new file will contain 2 methods that we can override. The “Up” method will contain all new database modifications. The “Down” will contain the rollback of the modifications. This way we can easily “jump” through database versions. We manually add the AddColumn and DropColumn statements.

namespace EF_DatabaseMigrations.Migrations
{
    public partial class AddBuildDate : DbMigration
    {
        public override void Up()
        {
            AddColumn("dbo.Cars", "BuildDate", c => c.DateTime(nullable: false));
        }

        public override void Down()
        {
            DropColumn("dbo.Cars", "BuildDate");
        }
    }
}

When we want to apply those modifications, we need to type “update-database -TargetMigration:AddBuildDate”. This will modify our database and add the new column.

 

If you want to rollback the database at it’s begin state, you type “update-database -TargetMigration:0” in the Package Console Manager.

What about the database adminstrators

We can’t forget these guys. They probably won’t like this way of creating the database and would probably prefer a script. Well you can add an extra parameter to the “update-database” called script that will create a clean SQL script that performs all the necessary steps to create the database.

“update-database –TargetMigration:AddBuildDate –script” will create this script:

ALTER TABLE [Cars] ADD [BuildDate] [datetime]
INSERT INTO [__MigrationHistory] ([MigrationId], [Model], [ProductVersion]) VALUES ('201208140904052_AddBuildDate', 0x1F8B0800000000000400ECBD07601C499625262F6DCA7B7F4AF54AD7E074A10880601324D8904010ECC188CDE692EC1D69472329AB2A81CA6556655D661640CCED9DBCF7DE7BEFBDF7DE7BEFBDF7BA3B9D4E27F7DFFF3F5C6664016CF6CE4ADAC99E2180AAC81F3F7E7C1F3F22FEC7BFF71F7CFC7BBC5B94E9655E3745B5FCECA3DDF1CE4769BE9C56B36279F1D947EBF67CFBE0A3DFE3E8374E1E9FCE16EFD29F34EDF6D08EDE5C369F7D346FDBD5A3BB779BE93C5F64CD78514CEBAAA9CEDBF1B45ADCCD66D5DDBD9D9D83BBBB3B777302F111C14AD3C7AFD6CBB658E4FC07FD79522DA7F9AA5D67E517D52C2F1BFD9CBE79CD50D317D9226F56D934FFECA3D367BFFFD3ACCD2659937F515CD4594B0835E3A74F3E4A8FCB22237C5EE7E5F97B22B7F310C87D64BBA58E4F09C1F6FACDF52AE7CE3FFBE824ABFD06D4E4F7CAAF830FE8A39775B5CAEBF6FA557EAEAF9DCD3E4AEF86EFDDEDBE685FF3DE41CFF4DBB2BDB7F751FA625D96D9A4A40FCEB3B2C93F4A579F3E7ADD5675FE79BECC8904F9EC65D6B679BDC4BB3963AE1478B4FAF47644787877670F44B89B2D9755CB54ED21DE4193E7CA60FABAAD89653E4A9F15EFF2D9F37C79D1CE2DB65F64EFCC27FB3B3BC4395F2D0B62317AABADD7B93F3CF97B73B74FD645392316C84DD7F8FD0D7153844E1D482FB2CBE282C7D61D4AF6965ABFCA4B61A779B1123E1AD3ACFFFEF2E5B3BA5ABCAA00DB7CF6FBBFAED6F51468549D2FDE64F545DE86DD3FBEEB586A23A371773FE2341F4DFCFBB3CD6843F373DC34D5B460349D26F8FD239374BA9CA5430C22982B43119EEBB22D566531A5FE3EFBE85BBDE147401996F240095F86B076C6E3DDEEB8BC11F4198F546F9B1534B73AB8A71368D74610EF6285175EE7ADA343F351EAA8E68FB037A0F055A01E7B5786D479D9C33FE85C05D3FBBA2BB45DA1D93045164333AE1089CD93E2BDAC23EB0A62388608DBD97970B6EFAE183F6324EF0E58C9C75F64AB1589846735F593F4B598CC93EDD7EF6F101702E3EEB489D8458BADED897444769177BEA5AE09D36745DDB4C6667F949ECC16BD66B7E13AD395C77CFD2932FC641AE3777961C86F607EED0072247C46A35A909EE301EAF00C93F4DF4AE1B064655647D4EC4955AE17CB2155BDE96DB5B33E00FDE8F6303CA3E9C3F13EEEC37A7CB743842EB93D8DA92D3B5CDF9DBB5BCDAC48D03732B53125708BB98DBFF6B333B962DAFCF7E5931FFE74842AAA276DAA6D6F4175D7F8164203B5DA214957BDF62971AB796010B1C900216C9FEF818E9A8AAF89CE7B6342EA7156B0DB71D6C071B15ECE2D06D93538FD09EFD99D6E13CB6ED6FE74ECCC63D5F98131626A7443B69E1190261FA534F4CB620603F0FABA69F3C5180DC6AF7F51F93AAF29263DC9C7FB08344DB32FB265719E37ED9BEA6D4EB69EBFFBBA419FF5429B6656FEBF30F22B96ADCACF26CFFBA6806753C4B6BCCCEAE99CDDD1AEDBFC8101D98C7E4778FFFEF8196EF668F0757CF6DE14FEDCC4543F2B7328E6E183A6F01B0C759C5178AFA02480C101C3FB86483447790D126625A9B0A6AD4989F52CE24B0A15A7C52A2B7D6CFBFAFB36F30EAA5970DD6F9EE6AB7C8909F546749B6E3698280BB2C37D378DFB3DC3BDBE7FDE9DA55ECC3714F2895227E99F54349DC27BB1502A1A0C0EC68231A8F118EB67234CF45173AEE10DB1613798FCD98906FBD6979863BDB4695562CAA6B87020E04F2CF369C016B6CDD9F2BC32ECD9C1C834E9AAEABCCD48CF67C7755B9C67D396BE9EE64DC3A9999FCCCA3535395D4CF2D9D9F2CB75BB5AB734E47C3129AF7D6280CB37F5CF216F88F3E32F57EC677E134320340B98AA2F976CC12CDECFFA6A780804C447753BE6B2858EBFB8B6905E54CB5B0252F23D3552FF265FAC281999375F2E5F6797F9306E37D330A4D8E3A74546CEFAA249EF1E09D3787C42EC4739FAA3FF270000FFFFEA4946EC18180000, '5.0.0-rc.net40')

The Visual Studio solution can be downloaded here.