In another post (How can I extract the database table and column name for a property on an EF4 entity?), I provided an answer (top of page) for extracting at run-time an Entity-Framework model's store info (table/column) from conceptual info (entity/property-expression). Usually, one doesn't need such functionality--but sometimes it's useful, like when one wants to add an additional index to an entity's table for a database created at run-time.
The drawback with my solution (see below) is that it requires that the MSL mapping info be in a separate file (i.e., the host project copies the metadata to the output path) or that its XML already be available. What if the MSL metadata is embedded as an assembly resource? (XElement methods can't resolve a "res://" path-name!) How does one get the mapping (or the XML for the mapping) then?
Any solution--whether it uses XML, metadata-workspace, or whatever--must work with projects targeted at pre-.NET 4.7 platforms (i.e., .NET 4.6.1), be able to work with any EF model (regardless of whether it's created Model-First, Database-First, or Code-First), and be capable of handling complex-property info (i.e., Entity.ComplexProperty. ... .ComplexSubProperty.ScalarProperty) as well as scalar-property info. Ideally, it should also work with DbContext-derived contexts (although an ObjectContext can be extracted from a DbContext) and be in VB.NET. (I'm currently using Visual Basic 2019 with EF 6.4, and my "host project" is targeted at .NET 4.6.1.)
For perspective, here is the code for my existing solution (remember the aforementioned limitations that I want to mitigate):
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
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>
Once again, my issue is getting store info (table/column) from conceptual info (entity/property-expression) when the MSL mapping info is embedded in the project as a resource (i.e., "res://SCTModel.msl")
