How does one get the table-column name for a complex property of an entity? (VB 2010/EF4)

Question

I've written the following code (VB 2010/EF 4) for getting storage (database) table/column names from (conceptual) entity/property names:

Imports System.Data.Objects
Imports System.Data.Entity
Imports System.Data.SqlClient
Imports System.Data.EntityClient
Imports System.Data.Metadata.Edm
Imports System.Data.Objects.DataClasses
Imports System.Linq.Expressions
Imports System.Runtime.Serialization
Imports System.Reflection

Public Class ConvertConceptualToStore
    Private Shared Function GetTableName(Of T As EntityObject)() As String
    Dim type As Type = GetType(T) : Dim at = GetAttribute(Of EdmEntityTypeAttribute)(type)
    Return at.Name
    End Function

    Private Shared Function GetColumnName(Of T As EntityObject) _
        (ByVal propertySelector As Expression(Of Func(Of T, Object))) As String

    If propertySelector Is Nothing Then
        Throw New Exception("""" & propertySelector.ToString & """ is null.")
    End If

    Dim propertyInfo As PropertyInfo = GetPropertyInfo(propertySelector.Body)
    Dim attribute As DataMemberAttribute = _
        GetAttribute(Of DataMemberAttribute)(propertyInfo)
    If String.IsNullOrEmpty(attribute.Name) Then
        Return propertyInfo.Name
    Else
        Return attribute.Name
    End If
    End Function

    Private Shared Function GetAttribute(Of T As Class) (ByVal memberInfo As MemberInfo) As T
    If memberInfo Is Nothing Then
        Throw New Exception("""" & memberInfo.ToString & """ is null.")
    End If

    Dim customAttributes() As Object = _
        memberInfo.GetCustomAttributes(GetType(T), False)
    Dim attribute As T = _
        DirectCast(customAttributes.Where(Function(a) TypeOf a Is T).First(), T)
    Return attribute
    End Function

    Private Shared Function GetPropertyInfo(ByVal propertySelector As Expression) As PropertyInfo
    If propertySelector Is Nothing Then
        Throw New Exception("""" & propertySelector.ToString & """ is null.")
    End If

    Dim memberExpression As MemberExpression = _
        TryCast(propertySelector, MemberExpression)
    If memberExpression Is Nothing Then
        Dim unaryExpression As UnaryExpression = _
            TryCast(propertySelector, UnaryExpression)
        If unaryExpression IsNot Nothing _
                AndAlso unaryExpression.NodeType = ExpressionType.Convert Then
            memberExpression = TryCast(unaryExpression.Operand, MemberExpression)
        End If
    End If
    If memberExpression IsNot Nothing _
            AndAlso memberExpression.Member.MemberType = MemberTypes.Property Then
        Return DirectCast(memberExpression.Member, PropertyInfo)
    Else
        Throw New ArgumentException("No property reference was found.", "propertySelector")
    End If
    End Function

    ' Invocation example
    'Public Shared Function Test()
    'Dim table As String = GetTableName(Of User)()
    'Dim column As String = GetColumnName(Of User)(Function(u) u.Name)
    'End Function
End Class

QUESTIONS:

1. Am I correct in assuming that the code DOESN'T require an ObjectContext or Data.CSSpace?
2. What if a column is a component of a Complex property? Should one do anything different then? (i.e., If Location is an entity with a complex property named Address, then how does one get the column name for, say, Address.Street?)

Answer 1

Here's a general-purpose algorithm for converting between conceptual and store information, written in Visual Basic 2010.

I have written a new routine that to convert an entity/property pair into a table/column pair. This class, MSLMappingAction, takes in its constructor the model name and either an XElement XML tree, an MSL mapping file, or an XML string. One then uses the ConceptualToStore method to take String specifying entity and property "expressions" (stored in the MSLConceptualInfo structure) and find the table and column names (stored in the MSLStoreInfo structure).

Notes:

1. One could also write a "StoreToConceptual" method to convert in the other direction, but the XML queries would probably be a bit more complex. The same goes for handling navigation-propery/function/stored-procedure mappings.
2. Beware inherited properties of derived entities! If a property is not specific to the derived entity, then you should use the base entity's name.)

Here's the code.

Host code: (For the XML sample given [see bottom], it returns table name "Locations" and column name "Address_Street" for the store information when given the entity "Location" and the property expression "Address.Street" [and the conceptual model name "SCTModel"]):

Dim MSL As MSLMappingAction = New MSLMappingAction(".\SCTModel.msl", "SCTModel")

Dim ConceptualInfo As MSLConceptualInfo = New MSLConceptualInfo With {.EntityName = "Location", .PropertyName = "Address.Street"}
Dim StoreInfo As MSLStoreInfo = MSL.ConceptualToStore(ConceptualInfo)
MessageBox.Show(StoreInfo.TableName & ": " & StoreInfo.ColumnName)

Class code:

Option Infer On
Imports System.Xml.Linq

''' <summary>
''' This class allows one to convert between an EF conceptual model's entity/property pair
''' and its database store's table/column pair.
''' </summary>
''' <remarks>It takes into account entity splitting and complex-property designations;
''' it DOES NOT take into account inherited properties
''' (in such a case, you should access the entity's base class)</remarks>
Public Class MSLMappingAction

'   private fields and routines
Private mmaMSLMapping As XElement
Private mmaModelName, mmaNamespace As String

Private Function FullElementName(ByVal ElementName As String) As String
'   pre-pend Namespace to ElementName
Return "{" & mmaNamespace & "}" & ElementName
End Function

Private Sub ValidateParams(ByVal MappingXML As XElement, Byval ModelName As String)
'   verify that model name is specified
If String.IsNullOrEmpty(ModelName) Then
    Throw New EntityException("Entity model name is not given!")
End If
'   verify that we're using C-S space
If MappingXML.@Space <> "C-S" Then
    Throw New MetadataException("XML is not C-S mapping data!")
End If
'   get Namespace and set private variables
mmaNamespace = MappingXML.@xmlns
mmaMSLMapping = MappingXML : mmaModelName = ModelName
End Sub

Private Function IsSequenceEmpty(Items As IEnumerable(Of XElement)) As Boolean
'   determine if query result is empty
Return _
    Items Is Nothing OrElse Items.Count = 0
End Function

'   properties
''' <summary>
''' Name of conceptual entity model
''' </summary>
''' <returns>Conceptual-model String</returns>
''' <remarks>Model name can only be set in constructor</remarks>
Public ReadOnly Property EntityModelName() As String
Get
    Return mmaModelName
End Get
End Property

''' <summary>
''' Name of mapping namespace
''' </summary>
''' <returns>Namespace String of C-S mapping layer</returns>
''' <remarks>This value is determined when the XML mapping
''' is first parsed in the constructor</remarks>
Public ReadOnly Property MappingNamespace() As String
Get
    Return mmaNamespace
End Get
End Property

'   constructors
''' <summary>
''' Get C-S mapping information for an entity model (with XML tree)
''' </summary>
''' <param name="MappingXML">XML mapping tree</param>
''' <param name="ModelName">Conceptual-model name</param>
''' <remarks></remarks>
Public Sub New(ByVal MappingXML As XElement, ByVal ModelName As String)
ValidateParams(MappingXML, ModelName)
End Sub

''' <summary>
''' Get C-S mapping information for an entity model (with XML file)
''' </summary>
''' <param name="MSLFile">MSL mapping file</param>
''' <param name="ModelName">Conceptual-model name</param>
''' <remarks></remarks>
Public Sub New(ByVal MSLFile As String, ByVal ModelName As String)
Dim MappingXML As XElement = XElement.Load(MSLFile)
ValidateParams(MappingXML, ModelName)
End Sub

'   methods
''' <summary>
''' Get C-S mapping infomration for an entity model (with XML String)
''' </summary>
''' <param name="XMLString">XML mapping String</param>
''' <param name="ModelName">Conceptual-model name</param>
''' <returns></returns>
Public Shared Function Parse(ByVal XMLString As String, ByVal ModelName As String)
Return New MSLMappingAction(XElement.Parse(XMLString), ModelName)
End Function

''' <summary>
''' Convert conceptual entity/property information into store table/column information
''' </summary>
''' <param name="ConceptualInfo">Conceptual-model data
''' (.EntityName = entity expression String, .PropertyName = property expression String)</param>
''' <returns>Store data (.TableName = table-name String, .ColumnName = column-name String)</returns>
''' <remarks></remarks>
Public Function ConceptualToStore(ByVal ConceptualInfo As MSLConceptualInfo) As MSLStoreInfo
Dim StoreInfo As New MSLStoreInfo
With ConceptualInfo
    '   prepare to query XML
    If Not .EntityName.Contains(".") Then
        '   make sure entity name is fully qualified
        .EntityName = mmaModelName & "." & .EntityName
    End If
    '   separate property names if there's complex-type nesting
    Dim Properties() As String = .PropertyName.Split(".")
    '   get relevant entity mapping
    Dim MappingInfo As IEnumerable(Of XElement) = _                 
        (From mi In mmaMSLMapping.Descendants(FullElementName("EntityTypeMapping")) _
            Where mi.@TypeName = "IsTypeOf(" & .EntityName & ")" _
                OrElse mi.@TypeName = .EntityName _
         Select mi)
    '   make sure entity is in model
    If IsSequenceEmpty(MappingInfo) Then
        Throw New EntityException("Entity """ & .EntityName & """ was not found!")
    End If
    '   get mapping fragments
    Dim MappingFragments As IEnumerable(Of XElement) = _
        (From mf In MappingInfo.Descendants(FullElementName("MappingFragment")) _
         Select mf)
    '   make sure there's at least 1 fragment
    If IsSequenceEmpty(MappingFragments) Then
        Throw New EntityException("Entity """ & .EntityName & """ was not mapped!")
    End If
    '   search each mapping fragment for the desired property
    For Each MappingFragment In MappingFragments
        '   get physical table for this fragment
        StoreInfo.TableName = MappingFragment.@StoreEntitySet
        '   search property expression chain
        Dim PropertyMapping As IEnumerable(Of XElement) = {MappingFragment}
        '   parse complex property info (if any)
        For index = 0 To UBound(Properties) - 1
            '   go down 1 level
            Dim ComplexPropertyName = Properties(index)
            PropertyMapping = _
                (From pm In PropertyMapping.Elements(FullElementName("ComplexProperty")) _
                    Where pm.@Name = ComplexPropertyName)
            '   verify that the property specified for this level exists
            If IsSequenceEmpty(PropertyMapping) Then
                Exit For 'go to next fragment if not
            End If
        Next index
        '   property not found? try next fragment
        If IsSequenceEmpty(PropertyMapping) Then
            Continue For
        End If
        '   parse scalar property info
        Dim ScalarPropertyName = Properties(UBound(Properties))
        Dim ColumnName As String = _
            (From pm In PropertyMapping.Elements(FullElementName("ScalarProperty")) _
                Where pm.@Name = ScalarPropertyName _
                Select CN = pm.@ColumnName).FirstOrDefault
        '   verify that scalar property exists
        If Not String.IsNullOrEmpty(ColumnName) Then
            '   yes? return (exit) with column info
            StoreInfo.ColumnName = ColumnName : Return StoreInfo
        End If
    Next MappingFragment
    '   property wasn't found
    Throw New EntityException("Property """ & .PropertyName _
        & """ of entity """ & .EntityName & """ was not found!")
End With
End Function
End Class

''' <summary>
''' Conceptual-model entity and property information  
''' </summary>
Public Structure MSLConceptualInfo
''' <summary>
''' Name of entity in conceptual model
''' </summary>
''' <value>Entity expression String</value>
''' <remarks>EntityName may or may not be fully qualified (i.e., "ModelName.EntityName");
''' when a mapping method is called by the MSLMappingAction class, the conceptual model's
''' name and a period will be pre-pended if it's omitted</remarks>
Public Property EntityName As String
''' <summary>
''' Name of property in entity
''' </summary>
''' <value>Property expression String</value>
''' <remarks>PropertyName may be either a stand-alone scalar property or a scalar property
''' within 1 or more levels of complex-type properties; in the latter case, it MUST be fully
''' qualified (i.e., "ComplexPropertyName.InnerComplexPropertyName.ScalarPropertyName")</remarks>
Public Property PropertyName As String
End Structure

''' <summary>
''' Database-store table and column information
''' </summary>
Public Structure MSLStoreInfo
''' <summary>
''' Name of table in database
''' </summary>
Public Property TableName As String
''' <summary>
''' Name of column in database table
''' </summary>
Public Property ColumnName As String
End Structure

The catch is that the node names all have to have a namespace prepended to them. It tripped me up until I checked my elements 1 at a time!

Here's the sample XML -- which I load from the ".\SCTModel.msl" file in the code above:

<?xml version="1.0" encoding="utf-8"?>
<Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs">
  <EntityContainerMapping StorageEntityContainer="SCTModelStoreContainer" CdmEntityContainer="SocialContactsTracker">
    <EntitySetMapping Name="SocialContacts">
      <EntityTypeMapping TypeName="IsTypeOf(SCTModel.SocialContact)">
        <MappingFragment StoreEntitySet="SocialContacts">
          <ScalarProperty Name="Id" ColumnName="Id" />
          <ScalarProperty Name="DateAdded" ColumnName="DateAdded" />
          <ScalarProperty Name="Information" ColumnName="Information" />
          <ComplexProperty Name="DefaultAssociations" TypeName="SCTModel.DefaultAssociations">
            <ScalarProperty Name="DefaultLocationID" ColumnName="DefaultAssociations_DefaultLocationID" />
            <ScalarProperty Name="DefaultEmailID" ColumnName="DefaultAssociations_DefaultEmailID" />
            <ScalarProperty Name="DefaultPhoneNumberID" ColumnName="DefaultAssociations_DefaultPhoneNumberID" />
            <ScalarProperty Name="DefaultWebsiteID" ColumnName="DefaultAssociations_DefaultWebsiteID" />
          </ComplexProperty>
          <ScalarProperty Name="Picture" ColumnName="Picture" />
        </MappingFragment>
      </EntityTypeMapping>
      <EntityTypeMapping TypeName="IsTypeOf(SCTModel.Person)">
        <MappingFragment StoreEntitySet="SocialContacts_Person">
          <ScalarProperty Name="Id" ColumnName="Id" />
          <ScalarProperty Name="DateOfBirth" ColumnName="DateOfBirth" />
          <ScalarProperty Name="FirstName" ColumnName="FirstName" />
          <ScalarProperty Name="LastName" ColumnName="LastName" />
        </MappingFragment>
      </EntityTypeMapping>
      <EntityTypeMapping TypeName="IsTypeOf(SCTModel.Organization)">
        <MappingFragment StoreEntitySet="SocialContacts_Organization">
          <ScalarProperty Name="Id" ColumnName="Id" />
          <ScalarProperty Name="Name" ColumnName="Name" />
          <ScalarProperty Name="DateOfCreation" ColumnName="DateOfCreation" />
        </MappingFragment>
      </EntityTypeMapping>
    </EntitySetMapping>
    <EntitySetMapping Name="Locations">
      <EntityTypeMapping TypeName="IsTypeOf(SCTModel.Location)">
        <MappingFragment StoreEntitySet="Locations">
          <ScalarProperty Name="Id" ColumnName="Id" />
          <ScalarProperty Name="City" ColumnName="City" />
          <ScalarProperty Name="State" ColumnName="State" />
          <ScalarProperty Name="ZIP" ColumnName="ZIP" />
          <ScalarProperty Name="Country" ColumnName="Country" />
          <ComplexProperty Name="Address" TypeName="SCTModel.Address">
            <ScalarProperty Name="Street" ColumnName="Address_Street" />
            <ScalarProperty Name="Apartment" ColumnName="Address_Apartment" />
            <ScalarProperty Name="HouseNumber" ColumnName="Address_HouseNumber" />
          </ComplexProperty>
        </MappingFragment>
      </EntityTypeMapping>
    </EntitySetMapping>
    <EntitySetMapping Name="PhoneNumbers">
      <EntityTypeMapping TypeName="IsTypeOf(SCTModel.PhoneNumber)">
        <MappingFragment StoreEntitySet="PhoneNumbers">
          <ScalarProperty Name="Id" ColumnName="Id" />
          <ScalarProperty Name="Number" ColumnName="Number" />
          <ScalarProperty Name="PhoneType" ColumnName="PhoneType" />
        </MappingFragment>
      </EntityTypeMapping>
    </EntitySetMapping>
    <EntitySetMapping Name="Emails">
      <EntityTypeMapping TypeName="IsTypeOf(SCTModel.Email)">
        <MappingFragment StoreEntitySet="Emails">
          <ScalarProperty Name="Id" ColumnName="Id" />
          <ScalarProperty Name="DomainName" ColumnName="DomainName" />
          <ScalarProperty Name="UserName" ColumnName="UserName" />
        </MappingFragment>
      </EntityTypeMapping>
    </EntitySetMapping>
    <EntitySetMapping Name="Websites">
      <EntityTypeMapping TypeName="IsTypeOf(SCTModel.Website)">
        <MappingFragment StoreEntitySet="Websites">
          <ScalarProperty Name="Id" ColumnName="Id" />
          <ScalarProperty Name="URL" ColumnName="URL" />
        </MappingFragment>
      </EntityTypeMapping>
    </EntitySetMapping>
    <AssociationSetMapping Name="SocialContactWebsite" TypeName="SCTModel.SocialContactWebsite" StoreEntitySet="SocialContactWebsite">
      <EndProperty Name="SocialContact">
        <ScalarProperty Name="Id" ColumnName="SocialContacts_Id" />
      </EndProperty>
      <EndProperty Name="Website">
        <ScalarProperty Name="Id" ColumnName="Websites_Id" />
      </EndProperty>
    </AssociationSetMapping>
    <AssociationSetMapping Name="SocialContactPhoneNumber" TypeName="SCTModel.SocialContactPhoneNumber" StoreEntitySet="SocialContactPhoneNumber">
      <EndProperty Name="SocialContact">
        <ScalarProperty Name="Id" ColumnName="SocialContacts_Id" />
      </EndProperty>
      <EndProperty Name="PhoneNumber">
        <ScalarProperty Name="Id" ColumnName="PhoneNumbers_Id" />
      </EndProperty>
    </AssociationSetMapping>
    <AssociationSetMapping Name="SocialContactEmail" TypeName="SCTModel.SocialContactEmail" StoreEntitySet="SocialContactEmail">
      <EndProperty Name="SocialContact">
        <ScalarProperty Name="Id" ColumnName="SocialContacts_Id" />
      </EndProperty>
      <EndProperty Name="Email">
        <ScalarProperty Name="Id" ColumnName="Emails_Id" />
      </EndProperty>
    </AssociationSetMapping>
    <AssociationSetMapping Name="SocialContactLocation" TypeName="SCTModel.SocialContactLocation" StoreEntitySet="SocialContactLocation">
      <EndProperty Name="SocialContact">
        <ScalarProperty Name="Id" ColumnName="SocialContacts_Id" />
      </EndProperty>
      <EndProperty Name="Location">
        <ScalarProperty Name="Id" ColumnName="Locations_Id" />
      </EndProperty>
    </AssociationSetMapping>
  </EntityContainerMapping>
</Mapping>