EF Migrations series

Most business applications use a database. Creating the database in the first place is the easy part. Continously evolving the database schema during development when multiple team members are doing changes in various parts of the database is one of the hard parts. The really hard part is to be able to upgrade – or revert back to – a specific version of the schema when needed.

In the first post in this series I created a basic database with the EF Code First features. One of the things I couldn’t do there was creating an index, so in this post I will look into adding an index to my database through EF Migrations in Entity Framework 4.3.

This post describes how to add an index separately. If the index is created together with there is a better way, as described in Indexes in Code-Based EF Migrations.

Enabling migrations

First we’re going to enable migrations.

PM> Enable-Migrations -EnableAutomaticMigrations
Code First Migrations enabled for project TestLib.
PM>

I’m always supicious about tools that alter my projects. A quick check with subversion for changes (yes, I do keep all the code for this blog under version control) shows that a Migrations directory has been added with a single file called Configurations.cs. (I’ve added some extra line breaks to avoid scroll bars)

namespace TestLib.Migrations
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Migrations;
    using System.Linq;
 
    internal sealed class Configuration :
        DbMigrationsConfiguration<TestLib.Entities.CarsContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
        }
 
        protected override void Seed(
            TestLib.Entities.CarsContext 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" }
            //    );
            //
        }
    }
}

It is tempting to add a SQL command to create the index directly into the Seed function, but it is the wrong place for schema changes. Instead I’ll add a new empty migration step where I can add my index creation.

PM> Add-Migration IX_Car_BrandId
Scaffolding migration 'IX_Car_BrandId'.
The Designer Code for this migration file includes a snapshot of your current
Code First model. This snapshot is used to calculate the changes to your model
when you scaffold the next migration. If you make additional changes to your
model that you want to include in this migration, then you can re-scaffold it
by running 'Add-Migration 201202201920484_IX_Car_BrandId' again.
PM>

Another file is added to my solution, called 201202201920484_IX_Car_BrandId.cs.

namespace TestLib.Migrations
{
    using System.Data.Entity.Migrations;
 
    public partial class IX_Car_BrandId : DbMigration
    {
        public override void Up()
        {
        }
 
        public override void Down()
        {
        }
    }
}

I’ll add my index creation to the Up() method and a drop statement to the Down() method to enable downgrading the database schema.

namespace TestLib.Migrations
{
    using System.Data.Entity.Migrations;
 
    public partial class IX_Car_BrandId : DbMigration
    {
        public override void Up()
        {
            CreateIndex("Car", new string[] { "BrandId", "RegistrationNumber" },
                true, "IX_Car_BrandId");
        }
 
        public override void Down()
        {
            DropIndex("Car", "IX_Car_BrandId");
        }
    }
}

I’ll apply the update through the Package Manager Console.

PM> update-database -verbose
Using NuGet project 'TestLib'.
Using StartUp project 'TestLib'.
Target database is: 'Cars' (DataSource: ., Provider: System.Data.SqlClient, 
  Origin: Configuration).
Applying explicit migrations: [201202201920484_IX_Car_BrandId].
Applying explicit migration: 201202201920484_IX_Car_BrandId.
CREATE UNIQUE INDEX [IX_Car_BrandId] 
  ON [Cars]([BrandId], [RegistrationNumber])
[Inserting migration history record]

The index have been properly created and a row is added to the system table __MigrationHistory indicating that the migration have been applied. Just to make things complete I will also try a downgrade to get the index dropped.

PM> update-database -verbose -targetmigration:InitialCreate
Using NuGet project 'TestLib'.
Using StartUp project 'TestLib'.
Target database is: 'Cars' (DataSource: ., Provider: System.Data.SqlClient, 
  Origin: Configuration).
Reverting migrations: [201202201920484_IX_Car_BrandId].
Reverting explicit migration: 201202201920484_IX_Car_BrandId.
DROP INDEX [IX_Car_BrandId] ON [Cars]
[Deleting migration history record]

Conclusion

The EF Migration package looks really promising. It solved the index problem that I got stuck at in the first post in this series. In later posts I will look into more features of EF Migrations.

This post is part of the EF Migrations series.<< Using Entity Framework to Create a DatabaseUpdating a Table with EF Migrations >>

Adding Indexes with EF Migrations

EF Migrations series

Enabling migrations

Conclusion

1 comment

Leave a comment Cancel reply