This is the fifth in a series of posts about the recently released Entity Framework Feature CTP4, now affectionately known as “EF Magic Unicorn Edition”.

For this post I’m going to assume you have a basic knowledge of Code First using DbContext.

One common question I hear is “how do I include additional indexes (or other db constructs) in a database generated by Code First?”. In this post I’m going to cover how to run some hardcoded DDL each time the database is created or re-created. I’m also putting together some code that solves the index question in a more generic way (allowing you to attribute the properties you want indexed).

Setup

You’ll need a reference to Microsoft.Data.Entity.Ctp.dll and System.Data.Entity.dll.

We are going to use the following derived context and single Product class. Our mission is to add an index to the column backing the Product.Code property.

public class ProductCatalog : DbContext
{
    public DbSet<Product> Products { get; set; }
}

public class Product
{
    public int Id { get; set; }
    public string Code { get; set; }
    public string Name { get; set; }
}

Creating an Initializer

In CTP4 we have the opportunity to register some logic to initialize the database for our context. We encapsulated this logic in a class that implements IDatabaseInitializer<TContext> and the logic will be executed the first time our context is constructed in each AppDomain.

I’m defining the initializer as a nested class inside the ProductCatalog so that we can make use of the protected ObjectContext property to execute commands. We could also have defined a property on ProductCatalog that  exposed the protected context or we could have just used context.Database.Connection within our initializer and run commands against the DbConnection (this would have required us to open and close connections etc.). Ultimately the functionality of ExecuteStoreCommand will be available from DbContext (probably via the DbContext.Database property) but it’s not there in CTP4.

public class ProductCatalog : DbContext
{
    public DbSet<Product> Products { get; set; }

    public class Initializer : IDatabaseInitializer<ProductCatalog>
    {
        public void InitializeDatabase(ProductCatalog context)
        {
            if (!context.Database.Exists() || !context.Database.ModelMatchesDatabase())
            {
                context.Database.DeleteIfExists();
                context.Database.Create();

                context.ObjectContext.ExecuteStoreCommand("CREATE INDEX IX_Products_Code ON dbo.Products ( Code )");
            }
        }
    }
}

Registering Our Initializer

Initializers are registered at the AppDomain level via the static method; System.Data.Entity.Infrastructure.Database.SetInitializer<TContext>(IDatabaseInitializer<TContext> strategy).

Here is a complete console application that shows all our code in action, including registering the initializer before any contexts are constructed in the AppDomain:

using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;

namespace AdditionalDDL
{
    class Program
    {
        static void Main(string[] args)
        {
            Database.SetInitializer<ProductCatalog>(new ProductCatalog.Initializer());

            using (var ctx = new ProductCatalog())
            {
                Console.WriteLine("{0} products exist in the database.", ctx.Products.Count());
            }

            Console.WriteLine("Press any key to exit.");
            Console.ReadKey();
        }
    }

    public class ProductCatalog : DbContext
    {
        public DbSet<Product> Products { get; set; }

        public class Initializer : IDatabaseInitializer<ProductCatalog>
        {
            public void InitializeDatabase(ProductCatalog context)
            {
                if (!context.Database.Exists() || !context.Database.ModelMatchesDatabase())
                {
                    context.Database.DeleteIfExists();
                    context.Database.Create();

                    context.ObjectContext.ExecuteStoreCommand("CREATE INDEX IX_Products_Code ON dbo.Products ( Code )");
                }
            }
        }
    }

    public class Product
    {
        public int Id { get; set; }
        public string Code { get; set; }
        public string Name { get; set; }
    }
}

Summary

In this post we covered running custom DDL each time our database is created by Code First, to achieve this we used a custom database initializer. I’m also working on a more generic initializer that is capable of adding indexes based on attributes in the class and will post about this later.