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; }
}

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);
                    }
                }
            }
        }
    }
}

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; }
}

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; }
}

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);
    }
}

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.



8 Comments

  • Alejandro says:

    Thanks for this article.

    The index isn’t generated for my table.
    For sure the problem is me because I’m a beginner with Code First.

    I use Entity Framework 5 and SQL Server 2012 Express.

    If I understood this article correctly, all I have to do is:
    Create the IndexAttribute class and replace the code with yours above.
    Create the IndexInitializer class and replace the code with yours above.
    Use the Index annotation in my attribute.

    In my case, the code for the entity is:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;

    namespace Datos
    {
    [Table(Clientes)]
    public class Cliente
    {
    public int ClienteId { get; set; }
    [Required(ErrorMessage=Debe escribir el Nombre)]
    public string Nombre { get; set; }
    [Required(ErrorMessage = Debe escribir el Primer Apellido)]
    public string Apellido1 { get; set; }
    public string Apellido2 { get; set; }
    [Index(NombreCompletoIndex, unique: true)]
    public string NombreCompleto { get; set; }
    public DateTime FechaRegistro { get; set; }
    public DateTime FechaUltimaCita { get; set; }
    public string Observaciones { get; set; }
    public string TipoClienteId { get; set; }
    public virtual TipoCliente TipoCliente { get; set; }
    public virtual ICollection Mascotas { get; set; }
    public virtual ICollection EmailsCliente { get; set; }
    public virtual ICollection TelefonosCliente { get; set; }
    public virtual ICollection CelularesCliente { get; set; }
    public virtual ICollection Eventos { get; set; }
    }
    }

    But when I search for Cliente’s indexes I just got IX_TipoClienteId and PK_dbo.Clientes
    Am I missing something?
    TIA

    • Damian Schenkelman says:

      You need to create the initializer and set it as the one to be used. For example:
      CompositeDatabaseInitializer<YourContext> compositeDatabaseInitializer = new CompositeDatabaseInitializer<YourContext>(
      new IndexInitializer<YourContext>());
      Database.SetInitializer(compositeDatabaseInitializer);

      After that, once you instantiate the context, the indexes should be correctly created.

      I hope this helps.

  • I found a bug in your code, first of you should move your context.Database.CreateIfNotExists(); call to the top and create a if statement around it and exit(return) the function if it returns false.

  • Mounhim says:

    Hi,

    I liked your code. I had a few problems with it though and maybe you can help.
    1st I had the same problem as Alejandro and executed your answer. Which didnt work because the indexes were created first and then the other initializer (my original one which recreated the database and tables). So I turned the order around in to having this in my global.asax.cs:

    var compositeDatabaseInitializer = new CompositeDatabaseInitializer(new MyDbInitializer(), new IndexInitializer());
    Database.SetInitializer(compositeDatabaseInitializer);

    var context = new myDbContext();
    compositeDatabaseInitializer.InitializeDatabase(context);

    That worked out in having the indexes created.

    But now I encounter another problem. Normally with unique constraints it is allowed to use them in a relationship with another entity as foreign key. Is there any way that I can achieve that with DataAnnotations?

  • CPicou says:

    Hi,
    I’ve got a custom initializer, so I use the CompositeDatabaseInitializer with the IndexInitializer and my custom initializer. It works fine.
    My problem is my custom initializer overrides a Seed method wich is not called by the CompositeDatabaseInitializer InitializeDatabase method.

    Do you have any suggestion ?
    Thank you
    Clément

  • michiel cornille says:

    Wouldn’t creating an index from code like this result in a TimeOutException?
    Long running queries that create indexes can take > 1 minute…

  • robert says:

    Interesting article, I was attempting something similar when I ran across this post. I approached it a little bit differently but have built out additional attributes for full text indexing, setting default constraints, and creating triggers on tables. They all work quite nicely but I did not even consider the layering of additional initializers. Very cool idea .

Leave a Reply