Entity Framework Code First Fluent Api: Adding Indexes to columns

After Migrations was introduced in EF 4.3 you can now add indexes when modifying or creating a table. Here is an excerpt from the EF 4.3 Code-Based Migrations Walkthrough from the ADO.NET team blog

namespace MigrationsCodeDemo.Migrations
{
    using System.Data.Entity.Migrations;

    public partial class AddPostClass : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "Posts",
                c => new
                    {
                        PostId = c.Int(nullable: false, identity: true),
                        Title = c.String(maxLength: 200),
                        Content = c.String(),
                        BlogId = c.Int(nullable: false),
                    })
                .PrimaryKey(t => t.PostId)
                .ForeignKey("Blogs", t => t.BlogId, cascadeDelete: true)
                .Index(t => t.BlogId)
                .Index(p => p.Title, unique: true);

            AddColumn("Blogs", "Rating", c => c.Int(nullable: false, defaultValue: 3));
        }

        public override void Down()
        {
            DropIndex("Posts", new[] { "BlogId" });
            DropForeignKey("Posts", "BlogId", "Blogs");
            DropColumn("Blogs", "Rating");
            DropTable("Posts");
        }
    }
}

This is a nice strongly typed way to add the indexes, which was what i was looking for when i first posted the question.

You could create an attribute called indexed (as you suggested), which is then picked up in a custom initializer.

I created the following attribute:

[AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = true)]
public class IndexAttribute : Attribute
{
    public IndexAttribute(bool isUnique = false, bool isClustered = false, SortOrder sortOrder = SortOrder.Ascending)
    {
        IsUnique = isUnique;
        IsClustered = isClustered;
        SortOrder = sortOrder == SortOrder.Unspecified ? SortOrder.Ascending : sortOrder;

    }

    public bool IsUnique { get; private set; }
    public bool IsClustered { get; private set; }
    public SortOrder SortOrder { get; private set; }
    //public string Where { get; private set; }
}

I then created a custom initializer which got a list of the table names created for the entities in my context. I have two base classes which all my entities inherit, so I did the following to get the table names:

 var baseEF = typeof (BaseEFEntity);
        var baseLink = typeof (BaseLinkTable);
        var types =
            AppDomain.CurrentDomain.GetAssemblies().ToList().SelectMany(s => s.GetTypes()).Where(
                baseEF.IsAssignableFrom).Union(AppDomain.CurrentDomain.GetAssemblies().ToList().SelectMany(
                    s => s.GetTypes()).Where(
                        baseLink.IsAssignableFrom));

        var sqlScript = context.ObjectContext.CreateDatabaseScript();

        foreach (var type in types)
        {
            var table = (TableAttribute) type.GetCustomAttributes(typeof (TableAttribute), true).FirstOrDefault();
            var tableName = (table != null ? table.Name : null) ?? Pluralizer.Pluralize(type.Name);

I then found all the properties on each entity that have this attribute and then execute a SQL command to generate the index on each property. Sweet!

//Check that a table exists
            if (sqlScript.ToLower().Contains(string.Format(CREATETABLELOOKUP, tableName.ToLower())))
            {

                //indexes

                var indexAttrib = typeof (IndexAttribute);
                properties = type.GetProperties().Where(prop => Attribute.IsDefined(prop, indexAttrib));
                foreach (var property in properties)
                {
                    var attributes = property.GetCustomAttributes(indexAttrib, true).ToList();

                    foreach (IndexAttribute index in attributes)
                    {
                        var indexName = string.Format(INDEXNAMEFORMAT, tableName, property.Name,
                                                      attributes.Count > 1
                                                          ? UNDERSCORE + (attributes.IndexOf(index) + 1)
                                                          : string.Empty);
                        try
                        {
                            context.ObjectContext.ExecuteStoreCommand(
                                string.Format(INDEX_STRING, indexName,
                                              tableName,
                                              property.Name,
                                              index.IsUnique ? UNIQUE : string.Empty,
                                              index.IsClustered ? CLUSTERED : NONCLUSTERED,
                                              index.SortOrder == SortOrder.Ascending ? ASC : DESC));
                        }
                        catch (Exception)
                        {
                        }
                    }
                }

I even went on to add class based indexes (which could have multiple columns) , unique constraints and default constraints all in the same way. Whats also really nice is that if you put these attributes on an inherited class the index or constraint gets applied to all the classes (tables) that inherit it.

BTW the pluralizer helper contains the following:

public static class Pluralizer
{
    private static object _pluralizer;
    private static MethodInfo _pluralizationMethod;

    public static string Pluralize(string word)
    {
        CreatePluralizer();
        return (string) _pluralizationMethod.Invoke(_pluralizer, new object[] {word});
    }

    public static void CreatePluralizer()
    {
        if (_pluralizer == null)
        {
            var aseembly = typeof (DbContext).Assembly;
            var type =
                aseembly.GetType(
                    "System.Data.Entity.ModelConfiguration.Design.PluralizationServices.EnglishPluralizationService");
            _pluralizer = Activator.CreateInstance(type, true);
            _pluralizationMethod = _pluralizer.GetType().GetMethod("Pluralize");
        }
    }
}

I've also looked into this recently and found no other way, so I settled with creating indexes when seeding the database:

public class MyDBInitializer : DropCreateDatabaseIfModelChanges<MyContext>
{
    private MyContext _Context;

    protected override void Seed(MyContext context)
    {
        base.Seed(context);
        _Context = context;

        // We create database indexes
        CreateIndex("FieldName", typeof(ClassName));

        context.SaveChanges();
    }

    private void CreateIndex(string field, Type table)
    {
        _Context.Database.ExecuteSqlCommand(String.Format("CREATE INDEX IX_{0} ON {1} ({0})", field, table.Name));
    }    
}   

To build on frozen's response, you can hand code it into a migration yourself.

First, go to the Package Manager Console and create a new migration with add-migration, then give it a name. A blank migration will appear. Stick this in:

    public override void Up()
    {
        CreateIndex("TableName", "ColumnName");
    }

    public override void Down()
    {
        DropIndex("TableName",new[] {"ColumnName"});
    }

Note that if you're using a string field it needs to be capped to a length of 450 chars as well.