EF CTP4 Tips & Tricks: Running Additional DDL

Posted on July 31, 2010. Filed under: Entity Framework, Visual Studio | Tags: , , , , , , , |

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

5 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 […]

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

One of those would really come in handy : )

Best Regards

[…] 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 […]

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.

Or if you want to create a composite index that acts as a unique constraint on 2 columns, here it is:

CREATE UNIQUE INDEX IX_Products_Unique_CodeAndName ON dbo.Product
(
[Code] ASC,
[Name] ASC
)


Where's The Comment Form?

    About

    Rowan works as a Program Manager for the ADO.NET Entity Framework team at Microsoft. He speaks at technical conferences and blogs at romiller.com. Rowan lives in Seattle, Washington with his wife Athalie. Prior to moving to the US he resided in the small state of Tasmania in Australia. Outside of technology Rowan's passions include snowboarding, mountain biking, horse riding, rock climbing and pretty much anything else that involves being active. The primary focus of his life, however, is to follow Jesus.

    RSS

    Subscribe Via RSS

    • Subscribe with Bloglines
    • Add your feed to Newsburst from CNET News.com
    • Subscribe in Google Reader
    • Add to My Yahoo!
    • Subscribe in NewsGator Online
    • The latest comments to all posts in RSS

    Meta

Liked it here?
Why not try sites on the blogroll...

Follow

Get every new post delivered to your Inbox.

Join 171 other followers

%d bloggers like this: