First, create an index using entity framework code (CTP5)

entity-framework

Question

Is it possible to instruct EF CTP5 to build an index when it builds a schema?

Update: To learn how EF 6.1 responds to this, see here (as pointed out by juFo below).

1
30
5/23/2017 12:06:41 PM

Accepted Answer

You may benefit from the new CTP5's ExecuteSqlCommand function on the Database class, which enables the execution of raw SQL queries against the database.

For this reason, a Seed method that has been overridden in a unique Initializer class is the appropriate location to call the SqlCommand function. For instance:

protected override void Seed(EntityMappingContext context)
{
    context.Database.ExecuteSqlCommand("CREATE INDEX IX_NAME ON ...");
}
31
7/21/2011 1:12:12 AM

Popular Answer

If you utilize migrations, there is a CreateIndex/DropIndex function, as several people noted in the comments to Morteza's response.

However, you may use the example from Morteza's response if you are in "debug" or "development" mode and are constantly altering the schema and producing new copies of the database.

In order to make things a bit simpler, I created a really straightforward extension technique to make it strongly typed. I'd want to share it with anybody who sees this question and would find this approach appealing. Simply alter it to suit your requirements and index naming conventions.

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: A [Index] property is now accessible starting with version 6.1.

Visit http://msdn.microsoft.com/en-US/data/jj591583#Index for additional information.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow