EF CTP4 Tips & Tricks: Running Additional DDL

Posted on July 31, 2010. Filed under: Entity Framework, Visual Studio | Tags: Code First, CTP4, DbContext, DDL, EF, Entity Framework, IDatabaseInitializer, ObjectContext |

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.

About these ads

Make a Comment

6 Responses to “EF CTP4 Tips & Tricks: Running Additional DDL”

RSS Feed for RoMiller.com Comments RSS Feed

[...] a flick through Google will eventually get you to this post at romiller.com. which basically suggests that you add a custom initializer class to your DbContext and wire it up [...]

EF4 Code First – Making Nice With Index’s | 2Bit Coder
January 12, 2011

Hey! Nice work! Did you ever succeed in creating an Index Attribute?

One of those would really come in handy : )

Best Regards

Johan Nilsson
January 27, 2011

It comes absolutely satisfactorily by enhanced contents.

アナルセックス
April 6, 2011

[...] a flick through Google will eventually get you to this post at romiller.com which basically suggests that you add a custom initializer class to your DbContext and wire it up [...]

EF4 Code First – Making Nice With Index’s - My Blog
February 21, 2012

The initialization as shown depends on the consumer (in this case the console app) explicitly setting the initializer.

The solution could be entirely self-contained by adding a static constructor to ProductCatalog that registers the initializer instead.

Eric J.
March 19, 2012