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.
6 Comments
I have a perfectly simple entity in my model, mapped directly to a table, except that one of the columns is used to filter the results, and so is not in the model (a IsDeleted column is checked to be 0 in the table mapping). but line 13 in your code throws an exception, because mapping.EntityTypeMappings has more than a single record. it has two, one with a single fragment in Fragments, and one with an empty Fragments collection. do you know why this is happening, and what to do about it?
Tsahi,
Can you post your mapping code for the table so that I can take a look?
We’re using Database First, so there’s no code mapping, just an .edmx file. basically for each type in the model, in the mapping pane, there is a line of filtering, which looks like [When IsDeleted = 0 ], and then for the IsDeleted column in the database side of the mapping (the Column column) there is nothing in the Value/Property side.
for the time being i changed it to
table = mapping.EntityTypeMappings.First(m => m.Fragments.Any()).Fragments.Single().StoreEntitySet;
but is this the right thing to do?
I am using code first, so I can’t really help too much. It is hard to find all the edge cases, so I’ll leave that up to you.
I would suggest changing First(m => ) to Single(m => ) if you are expecting one item. First might cause weird side effects down the road if you end up with multiple matches. Single will throw an error – forcing you to figure out what’s changed.
I’ll try your advice, and try to figure out this issue when I have more time. Thanks.
Late to the party, but I came across this article while working on the same issue, and I hit the same problem that tsahi did. I’m using EF 6.2 and my model has three levels of inheritance. Properties are defined in the top two levels, but not in the leaf class. This is the first time I have had to peek under the hood at the entity mappings, but it seems like the leaf class with no mappable Properties is resulting in a mapping with no Fragments.