Entity Framework • Entity Framework Code First • SQL Indexes

Creating Indexes via Data Annotations with Entity Framework 5.0

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:

  1. 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).
  2. 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.

using System;
[AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = true)]
public class IndexAttribute : Attribute
{
public IndexAttribute(string name, bool unique = false)
{
this.Name = name;
this.IsUnique = unique;
}
public string Name { get; private set; }
public bool IsUnique { get; private set; }
}
view raw IndexAttribute.cs hosted with ❤ by GitHub

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.

using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Linq;
using System.Reflection;
public class IndexInitializer<T> : IDatabaseInitializer<T> where T : DbContext
{
private const string CreateIndexQueryTemplate = "CREATE {unique} INDEX {indexName} ON {tableName} ({columnName})";
public void InitializeDatabase(T context)
{
const BindingFlags PublicInstance = BindingFlags.Public | BindingFlags.Instance;
foreach (var dataSetProperty in typeof(T).GetProperties(PublicInstance).Where(
p => p.PropertyType.Name == typeof(DbSet<>).Name))
{
var entityType = dataSetProperty.PropertyType.GetGenericArguments().Single();
TableAttribute[] tableAttributes = (TableAttribute[])entityType.GetCustomAttributes(typeof(TableAttribute), false);
foreach (var property in entityType.GetProperties(PublicInstance))
{
IndexAttribute[] indexAttributes = (IndexAttribute[])property.GetCustomAttributes(typeof(IndexAttribute), false);
NotMappedAttribute[] notMappedAttributes = (NotMappedAttribute[])property.GetCustomAttributes(typeof(NotMappedAttribute), false);
if (indexAttributes.Length > 0 && notMappedAttributes.Length == 0)
{
ColumnAttribute[] columnAttributes = (ColumnAttribute[])property.GetCustomAttributes(typeof(ColumnAttribute), false);
foreach (var indexAttribute in indexAttributes)
{
string indexName = indexAttribute.Name;
string tableName = tableAttributes.Length != 0 ? tableAttributes[0].Name : dataSetProperty.Name;
string columnName = columnAttributes.Length != 0 ? columnAttributes[0].Name : property.Name;
string query = CreateIndexQueryTemplate.Replace("{indexName}", indexName)
.Replace("{tableName}", tableName)
.Replace("{columnName}", columnName)
.Replace("{unique}", indexAttribute.IsUnique ? "UNIQUE" : string.Empty);
context.Database.CreateIfNotExists();
context.Database.ExecuteSqlCommand(query);
}
}
}
}
}
}
view raw IndexInitializer.cs hosted with ❤ by GitHub

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:

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
public class Customer
{
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid Id { get; set; }
[Index("NameIndex", unique: true)]
[MaxLength(128)]
public string Name { get; set; }
}
view raw Customer.cs hosted with ❤ by GitHub

The following table is created.

image

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

[Table("Clients")]
public class Customer
{
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid Id { get; set; }
[Index("NameIndex", unique: true)]
[MaxLength(128)]
[Column("CustomerName")]
public string Name { get; set; }
}
view raw CustomerTable.cs hosted with ❤ by GitHub

We get the following index:

image

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.

using System.Collections.Generic;
using System.Data.Entity;
public class CompositeDatabaseInitializer<T> : IDatabaseInitializer<T> where T : DbContext
{
private readonly List<IDatabaseInitializer<T>> initializers;
public CompositeDatabaseInitializer(params IDatabaseInitializer<T>[] databaseInitializers)
{
this.initializers = new List<IDatabaseInitializer<T>>();
this.initializers.AddRange(databaseInitializers);
}
public void InitializeDatabase(T context)
{
foreach (var databaseInitializer in this.initializers)
{
databaseInitializer.InitializeDatabase(context);
}
}
public void AddInitializer(IDatabaseInitializer<T> databaseInitializer)
{
this.initializers.Add(databaseInitializer);
}
public void RemoveInitializer(IDatabaseInitializer<T> databaseInitializer)
{
this.initializers.Remove(databaseInitializer);
}
}
view raw CompositeDatabaseInitializer.cs hosted with ❤ by GitHub

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.