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