Map Entity Framework code properties to database columns (CSpace to SSpace)

Question

I am trying to implement an attribute that I can apply to properties in my code first Entity Framework data model to indicate unique constraints that will be applied when the database is created. I have read about extracting EF table mapping information using the mapping API exposed for EF 6.1, and I have read about implementing a custom attribute to indicate which properties represent a natural key. But, unless I'm misreading the code, I think this will only work when the CLR property name (OSpace / CSpace) matches the SQL column name (SSpace). I would like to be able to include association properties (foreign keys) in my unique key like EnumList in this example:

Public Class EnumValue
   Public Property EnumValueId As Integer
   Public Property Sequence As Integer
   Public Overridable Property Label As TranslatedString
   <MaxLength(5), MyUnique()> _
   Public Property Value As String
   <MyUnique()> _
   Public Overridable Property EnumList As EnumList
End Class

I've started with this much code, which it based on the linked article about mapping table names

Dim ws = DirectCast(context, System.Data.Entity.Infrastructure.IObjectContextAdapter).ObjectContext.MetadataWorkspace
Dim oSpace = ws.GetItemCollection(Core.Metadata.Edm.DataSpace.OSpace)
Dim entityTypes = oSpace.GetItems(Of EntityType)()
Dim entityContainer = ws.GetItems(Of EntityContainer)(DataSpace.CSpace).Single()
Dim mapping = ws.GetItems(Of EntityContainerMapping)(DataSpace.CSSpace).Single.EntitySetMappings
For Each setType In entityTypes
   Dim cSpaceEntitySet = entityContainer.EntitySets.Single(Function(t) t.ElementType.Name = setType.Name)
   Dim sSpaceEntitySet = mapping.Single(Function(t) t.EntitySet Is cSpaceEntitySet)
   Dim tableInfo = sSpaceEntitySet.EntityTypeMappings.Single.Fragments.Single
   Dim tableName = If(tableInfo.StoreEntitySet.Table, tableInfo.StoreEntitySet.Name)
   Dim schema = tableInfo.StoreEntitySet.Schema

And that's enough to get the information I need about the table name, but now I need to somehow link the CLR property name to the SQL column name, and it's slow going understanding the EF metadata framework. I'm hoping that someone more familiar with it might speed things along.

score 3 · Accepted Answer · answered Apr 28 '14 at 17:03

I'm not sure if it's complete or reliable, but I finally worked out the code that works for my first test case:

Dim ws = DirectCast(context, System.Data.Entity.Infrastructure.IObjectContextAdapter).ObjectContext.MetadataWorkspace
Dim oSpace = ws.GetItemCollection(Core.Metadata.Edm.DataSpace.OSpace)
Dim entityTypes = oSpace.GetItems(Of EntityType)()
Dim entityContainer = ws.GetItems(Of EntityContainer)(DataSpace.CSpace).Single()
Dim entityMapping = ws.GetItems(Of EntityContainerMapping)(DataSpace.CSSpace).Single.EntitySetMappings
Dim associations = ws.GetItems(Of EntityContainerMapping)(DataSpace.CSSpace).Single.AssociationSetMappings
For Each setType In entityTypes
   Dim cSpaceEntitySet = entityContainer.EntitySets.SingleOrDefault( _
      Function(t) t.ElementType.Name = setType.Name)
   If cSpaceEntitySet Is Nothing Then Continue For ' Derived entities will be skipped
   Dim sSpaceEntitySet = entityMapping.Single(Function(t) t.EntitySet Is cSpaceEntitySet)
   Dim tableInfo As MappingFragment
   If sSpaceEntitySet.EntityTypeMappings.Count = 1 Then
      tableInfo = sSpaceEntitySet.EntityTypeMappings.Single.Fragments.Single
   Else
      ' Select only the mapping (esp. PropertyMappings) for the base class
      tableInfo = sSpaceEntitySet.EntityTypeMappings.Where(Function(m) m.IsOfEntityTypes.Count _
         = 1 AndAlso m.IsOfEntityTypes.Single.Name Is setType.Name).Single().Fragments.Single
   End If
   Dim tableName = If(tableInfo.StoreEntitySet.Table, tableInfo.StoreEntitySet.Name)
   Dim schema = tableInfo.StoreEntitySet.Schema
   Dim clrType = Type.GetType(setType.FullName)
   Dim uniqueCols As IList(Of String) = Nothing
   For Each propMap In tableInfo.PropertyMappings.OfType(Of ScalarPropertyMapping)()
      Dim clrProp = clrType.GetProperty(propMap.Property.Name)
      If Attribute.GetCustomAttribute(clrProp, GetType(UniqueAttribute)) IsNot Nothing Then
         If uniqueCols Is Nothing Then uniqueCols = New List(Of String)
         uniqueCols.Add(propMap.Column.Name)
      End If
   Next
   For Each navProp In setType.NavigationProperties
      Dim clrProp = clrType.GetProperty(navProp.Name)
      If Attribute.GetCustomAttribute(clrProp, GetType(UniqueAttribute)) IsNot Nothing Then
         Dim assocMap = associations.SingleOrDefault(Function(a) _
            a.AssociationSet.ElementType.FullName = navProp.RelationshipType.FullName)
         Dim sProp = assocMap.Conditions.Single
         If uniqueCols Is Nothing Then uniqueCols = New List(Of String)
         uniqueCols.Add(sProp.Column.Name)
      End If
   Next
   If uniqueCols IsNot Nothing Then
      Dim propList = uniqueCols.ToArray()
      context.Database.ExecuteSqlCommand("CREATE UNIQUE INDEX IX_" & tableName & "_" & String.Join("_", propList) _
         & " ON " & schema & "." & tableName & "(" & String.Join(",", propList) & ")")
   End If
Next

Map Entity Framework code properties to database columns (CSpace to SSpace)

1 Answers1

Linked