Programmatic non-clustered indexes

I recently finished building an EntityFrameworkDatabaseBuilder that uses LINQ-style code to allow you to setup non-clustered indexes on a Sql Server instance through an Entity Framework database intializer. The calling code looks like this:

this.databaseBuilder.CreateIndex<User>(x => x.EmailAddress, x => x.Password); this.databaseBuilder.CreateIndex<Visitor>(x => x.SessionId);

This code will create two indexes. The first will be a dual-column, non-clustered index using EmailAddress and Password, and it will include the remaining columns from the User table. The second creates a non-clustered index on SessionId and includes the remaining columns from the Visitor table. I’ll talk about these indexes later – first, I wanted to post some code that fixes an issue with derived types when determining the table name from the Entity Framework mappings.

I read this article when researching ways to grab the table name from the mappings. It works great, except in the case of derived types. My User model is derived from a Person model. I still need to grab the table name; however, the EntitySetMapping will return null when at this line:

var mapping = metadata.GetItems<EntityContainerMapping>(DataSpace.CSSpace).Single().EntitySetMappings.Single(s => s.EntitySet == entitySet);

This occurs because the Entity Framework does not store the User table as an EntitySetMapping, but rather as an EntityTypeMapping as part of the People EntitySetMapping.

Here is the fully updated code to use with inheritance-based database models.

protected virtual string GetTableName(Type type) { var metadata = ((IObjectContextAdapter)this.context).ObjectContext.MetadataWorkspace; var objectItemCollection = ((ObjectItemCollection)metadata.GetItemCollection(DataSpace.OSpace)); var entityType = metadata.GetItems<EntityType>(DataSpace.OSpace).Single(e => objectItemCollection.GetClrType(e) == type); var entitySet = metadata.GetItems(DataSpace.CSpace).Where(x => x.BuiltInTypeKind == BuiltInTypeKind.EntityType).Cast<EntityType>().Single(x => x.Name == entityType.Name); var entitySetMappings = metadata.GetItems<EntityContainerMapping>(DataSpace.CSSpace).Single().EntitySetMappings.ToList(); EntitySet table; var mapping = entitySetMappings.SingleOrDefault(x => x.EntitySet.Name == entitySet.Name); if (mapping != null) { table = mapping.EntityTypeMappings.Single().Fragments.Single().StoreEntitySet; } else { mapping = entitySetMappings.SingleOrDefault(x => x.EntityTypeMappings.Where(y => y.EntityType != null).Any(y => y.EntityType.Name == entitySet.Name)); if (mapping != null) { table = mapping.EntityTypeMappings.Where(x => x.EntityType != null).Single(x => x.EntityType.Name == entityType.Name).Fragments.Single().StoreEntitySet; } else { var entitySetMapping = entitySetMappings.Single(x => x.EntityTypeMappings.Any(y => y.IsOfEntityTypes.Any(z => z.Name == entitySet.Name))); table = entitySetMapping.EntityTypeMappings.First(x => x.IsOfEntityTypes.Any(y => y.Name == entitySet.Name)).Fragments.Single().StoreEntitySet; } } return (string)table.MetadataProperties["Table"].Value ?? table.Name; }

I hope this will help someone looking for a similar solution.