none
Find name of mapped database column for a property of an Entity RRS feed

  • Question

  • Hello,

    what would be the correct way to find the column name where a property is mapped to?

    I tried something along the lines of this:

    var mdw = ctx.ObjectContext.MetadataWorkspace;
    var col = mdw.GetItemCollection(DataSpace.CSpace);
    var result = mdw.GetItems(DataSpace.CSSpace);
    
    

    but I cannot find anything usable in result.

    How to do it correctly?

    Regards
    daProgramma

         
    Friday, November 30, 2012 9:28 AM

Answers

  • Hello Alexander,

    I could not resist to spend a part of my weekend to look for a solution. I came up with the following, which prints a list of the property names of an entity and then a list of database columns for that entity. In the cases I have to deal with there always is a 1:1 relation between property name and database column. The code shows the principle only and must be developed further.

    My 2 cents...
    daProgramma

          var mdw = ctx.ObjectContext.MetadataWorkspace;
    
          {
            var col = mdw.GetItemCollection(DataSpace.OSpace);
    
            var result = mdw.GetItems<EntityType>(DataSpace.OSpace);
    
            var obj = result.Single( x => x.Name == "Address" );
            obj.Properties.ForEach( x => logger.Info( x.Name ) );
            
          }
    
          {
            var sspaceEntitySets = mdw
                             .GetItems<EntityContainer>(DataSpace.SSpace)
                             .First().BaseEntitySets.OfType<EntitySet>();
          
            var entitySet = sspaceEntitySets.Single( x => x.Name == "Address" );
         
            entitySet.ElementType.Properties.ForEach( x => logger.Info( x.Name ) );
    
            //-- EInige interessante INfos zum Typ, aber leider keine Members
            var mdp = entitySet.ElementType.MetadataProperties;
          }
    
    

    • Marked as answer by daProgramma Monday, December 3, 2012 3:01 PM
    Monday, December 3, 2012 9:47 AM

All replies

  • Hi daProgramma,

    Welcome to the MSDN forum.

    I have tried to use StorageMappingItemCollection class to get the entity container but also failed. Since the type is internal, I cannot access the properties. Please refer to this page for more information: http://entityframework.codeplex.com/discussions/399925

    Thus, I recommend you search the CS mapping part in edmx file directly, which is easier to do.

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, December 3, 2012 7:50 AM
  • Hello Alexander,

    I could not resist to spend a part of my weekend to look for a solution. I came up with the following, which prints a list of the property names of an entity and then a list of database columns for that entity. In the cases I have to deal with there always is a 1:1 relation between property name and database column. The code shows the principle only and must be developed further.

    My 2 cents...
    daProgramma

          var mdw = ctx.ObjectContext.MetadataWorkspace;
    
          {
            var col = mdw.GetItemCollection(DataSpace.OSpace);
    
            var result = mdw.GetItems<EntityType>(DataSpace.OSpace);
    
            var obj = result.Single( x => x.Name == "Address" );
            obj.Properties.ForEach( x => logger.Info( x.Name ) );
            
          }
    
          {
            var sspaceEntitySets = mdw
                             .GetItems<EntityContainer>(DataSpace.SSpace)
                             .First().BaseEntitySets.OfType<EntitySet>();
          
            var entitySet = sspaceEntitySets.Single( x => x.Name == "Address" );
         
            entitySet.ElementType.Properties.ForEach( x => logger.Info( x.Name ) );
    
            //-- EInige interessante INfos zum Typ, aber leider keine Members
            var mdp = entitySet.ElementType.MetadataProperties;
          }
    
    

    • Marked as answer by daProgramma Monday, December 3, 2012 3:01 PM
    Monday, December 3, 2012 9:47 AM
  • 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 Linq-to-XML queries would probably be a bit more complex. The same goes for handling mapping of navigation properties, functions, and stored procedures.
    2. Be careful when working with properties inherited by derived entities! If a property isn't specific to the derived entity, then you should use the name of the base entity.

    Here's the sample XML -- which I load from the ".\SCTModel.msl" file in the code (see next post):

    <?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>

    The code follows below


    Robert Gustafson


    Robert Gustafson

    Monday, August 4, 2014 7:41 AM

  • And here's the code (see previous post for sample XML):

    Host code (It returns table name "Locations" and column name "Address_Street" for the store information--when given the model name "SCTModel", the entity "Location", and the property expression "Address.Street"):

    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 mmaModelNamemmaNamespace As String
     
    Private Function FullElementName(ByVal ElementName As StringAs String
    '   pre-pend Namespace to ElementName
    Return "{" & mmaNamespace & "}" & ElementName
    End Function
     
    Private Sub ValidateParams(ByVal MappingXML As XElementByval ModelName As String)
    '   verify that model name is specified
    If String.IsNullOrEmpty(ModelNameThen
    	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 XElementByVal ModelName As String)
    ValidateParams(MappingXMLModelName)
    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 StringByVal ModelName As String)
    Dim MappingXML As XElement = XElement.Load(MSLFile)
    ValidateParams(MappingXMLModelName)
    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 StringByVal 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 MSLConceptualInfoAs 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(MappingInfoThen
    		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(MappingFragmentsThen
    		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(PropertyMappingThen
    				Exit For 'go to next fragment if not
    			End If
    		Next index
    		'   property not found? try next fragment
    		If IsSequenceEmpty(PropertyMappingThen
    			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(ColumnNameThen
    			'   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 XML elements 1 at a time!


    Robert Gustafson



    Robert Gustafson

    • Proposed as answer by Veeresh Angadi Thursday, March 29, 2018 12:55 PM
    Monday, August 4, 2014 7:43 AM