This week, for a project at Southworks, my friend Mariano Converti and I had to create indexes in a SQL database for two string type properties of entities that were part of an Entity Framework Code First model. As we were also required to support changes to the model after the application was deployed, we decided to use Entity Framework Code Base Migrations.

This approach successfully satisfied our requirements, but it required us to add additional migrations to specify the indexes. Thus, I started to research to see if there where other ways to do this, thinking that there was probably an available data annotation to get this done. To my surprise, I wasn't able to find such an attribute when searching the web (for example, this is the list of available Data Annotations). Once I realized this, I decided to create the necessary components on my own.

Update: After a quick twitter discussion with @julielerman, I decided to be more specific as to why I think using an attribute is better than code first migrations: Code first migrations create a class per migration which results in too many classes. While developing your model changes a lot, so having to create an extra class whenever you discover that you want to add an index is not really confortable (once you have deployed to production however, migrations are probably the way to go). Attributes are more descriptive. By just looking at the entity you can determine the indexes that will be created for it, without the need to check a separate class' code.

Creating the components

I found a couple of StackOverflow questions (here and here) from where I got some really useful ideas, such as using the ExecuteSqlCommand method introduced in Entity Framework 4.1 and detecting the attributes in a DataBaseInitializer.

The attribute code is really simple. It forces you to define the index name and gives the possibility of specifying whether the index is unique or not.

And the Initializer basically goes through the DbSet<> properties in the DbContext to retrieve the entity table names and check for the Index attribute in the properties in those entities.

Trying it out

I created a simple console application and ran a couple of tests to see if the data base was correctly created. For example, when using the following entity:

The following table is created.

If, for example the Table and Column name are customized:

We get the following index:

What if I already have an Initializer?

Well, in this case, a simple approach would be to add the IndexInitializer logic to your own Initializer, but that is not the only way to support the IndexAttribute and keep your initialization logic. During the week I also put together a simple CompositeDatabaseInitializer that can be used to separate the logic in different initializers. You could, for instance, have the IndexInitializer and another one to generate data.

Wrapping up

There are a lot of improvement opportunities for this implementation (such as adding support for clustered indexes). Nevertheless, features such as support for custom database initializers and the ability to execute SQL statements from the DbContext provide really powerful extensibility capabilities.

I hope that you can either use the code as is or take some useful ideas out of it.