Add index with entity framework code first (CTP5)

Question

Is there a way to get EF CTP5 to create an index when it creates a schema?

Update: See here for how EF 6.1 handles this (as pointed out by juFo below).

Answer 1

You can take advantage of the new CTP5’s ExecuteSqlCommand method on Database class which allows raw SQL commands to be executed against the database.

The best place to invoke SqlCommand method for this purpose is inside a Seed method that has been overridden in a custom Initializer class. For example:

protected override void Seed(EntityMappingContext context)
{
    context.Database.ExecuteSqlCommand("CREATE INDEX IX_NAME ON ...");
}

Answer 2

As some mentioned in the comments to Mortezas answer there is a CreateIndex/DropIndex method if you use migrations.

But if you are in "debug"/development mode and is changing the schema all the time and are recreating the database every time you can use the example mentioned in Morteza answer.

To make it a little easier, I have written a very simple extension method to make it strongly typed, as inspiration that I want to share with anyone who reads this question and maybe would like this approach aswell. Just change it to fit your needs and way of naming indexes.

You use it like this: context.Database.CreateUniqueIndex<User>(x => x.Name);

.

    public static void CreateUniqueIndex<TModel>(this Database database, Expression<Func<TModel, object>> expression)
    {
        if (database == null)
            throw new ArgumentNullException("database");

        // Assumes singular table name matching the name of the Model type

        var tableName = typeof(TModel).Name;
        var columnName = GetLambdaExpressionName(expression.Body);
        var indexName = string.Format("IX_{0}_{1}", tableName, columnName);

        var createIndexSql = string.Format("CREATE UNIQUE INDEX {0} ON {1} ({2})", indexName, tableName, columnName);

        database.ExecuteSqlCommand(createIndexSql);
    }

    public static string GetLambdaExpressionName(Expression expression)
    {
        MemberExpression memberExp = expression as MemberExpression;

        if (memberExp == null)
        {
            // Check if it is an UnaryExpression and unwrap it
            var unaryExp = expression as UnaryExpression;
            if (unaryExp != null)
                memberExp = unaryExp.Operand as MemberExpression;
        }

        if (memberExp == null)
            throw new ArgumentException("Cannot get name from expression", "expression");

        return memberExp.Member.Name;
    }

---

Update: From version 6.1 and onwards there is an [Index] attribute available.

For more info, see http://msdn.microsoft.com/en-US/data/jj591583#Index

Answer 3

This feature should be available in the near-future via data annotations and the Fluent API. Microsoft have added it into their public backlog:

http://entityframework.codeplex.com/workitem/list/basic?keywords=DevDiv [Id=87553]

Until then, you'll need to use a seed method on a custom Initializer class to execute the SQL to create the unique index, and if you're using code-first migrations, create a new migration for adding the unique index, and use the CreateIndex and DropIndex methods in your Up and Down methods for the migration to create and drop the index.

Answer 4

Check my answer here Entity Framework Code First Fluent Api: Adding Indexes to columns this allows you to define multi column indexes by using attributes on properties.