When programmatically get store info from conceptual info of an EF model, how does one get the COLUMN name corresponding to an entity property that's nested within a COMPLEX property RRS feed

  • Question


    Visual Basic 2019, .NET 4.6.1, Entity Framework 6.4

    VB.NET project with EF model created Model First, targeted at .NET 4.6.1.


    Sometimes it's useful to get the EF store model's table and column names at run-time from the conceptual model's entity and property names, respectively. The problem with using the metadata workspaces is when the column corresponds to a property that's nested within 1 or more levels of complex-property types.

    Imagine the following model (for the sake of argument; in real life, something this "simple" wouldn't look like this):


         Scalar1 -- (terminal) scalar property, and

         Complex1 -- complex property, with the following:

              Scalar2 -- (terminal) scalar property, and

              Complex2 -- complex property, with the following:

                   Scalar3 -- (terminal) scalar property

    Obviously, the 3 columns in the store correspond to "Scalar1", "Complex1.Scalar2", and "Complex1.Complex2.Scalar3". The code below will return the store-column name only in the first case, where we're dealing with a simple scalar; in the latter 2 cases, it returns to conceptual (terminal) property name, not the store-column name corresponding to it.

    When not dealing with ScalarPropertyMapping, I can't seem to generate an object expression that supports a Column property, or gets a ScalarPropertyMapping which does. The collection of ScalarPropertyMapping can't be searched for a nested-property expression, like "Complex1.Scalar2", and the types PropertyMapping and ComplexPropertyMapping not only don't support the Column property (not surprising), but don't have members from which one can, directly or indirectly, obtain an object which does.

    It's as if EF is trying to restrict access to column names to cases where they apply to unnested elements in the conceptual model! Since my EF model uses complex properties and I need access store column names even when the corresponding conceptual property is nested within a complex type, I'm at an impasse! 

    Option Infer On
    Imports System
    Imports System.Collections.Generic
    Imports System.Data.Entity
    Imports System.Data.Entity.Core.Mapping
    Imports System.Data.Entity.Core.Metadata.Edm
    Imports System.Data.Entity.Infrastructure
    Imports System.Linq
    Friend Class Program
    	Public Shared Function GetColumnName(ByVal Entity As Type, _
    		ByVal propertyName As String, ByVal context As DbContext) As String
    	Dim metadata As MetadataWorkspace = _
    		DirectCast(context, IObjectContextAdapter).ObjectContext.MetadataWorkspace
    	'   Get the part of the model that contains info about the actual CLR types
    	Dim objectItemCollection As ObjectItemCollection = _
    		(CType(metadata.GetItemCollection(DataSpace.OSpace), ObjectItemCollection))
    	'   Get the entity type from the model that maps to the CLR type
    	Dim entityType As EntityType = _
    		metadata.GetItems(Of EntityType)(DataSpace.OSpace) _
    			.Single(Function(e) objectItemCollection.GetClrType(e) Is Entity)
    	'   Get the entity set that uses this entity type
    	Dim entitySet As EntitySet = _
    		metadata.GetItems(Of EntityContainer)(DataSpace.CSpace) _
    			.Single().EntitySets.Single(Function(s) s.ElementType.Name = entityType.Name)
    	'   Find the mapping between conceptual and storage model for this entity set
    	Dim mapping As EntitySetMapping = _
    		metadata.GetItems(Of EntityContainerMapping)(DataSpace.CSSpace) _
    			.Single().EntitySetMappings.Single(Function(s) s.EntitySet.Equals(entitySet))
    	'   Find the storage entity set (table) that the entity is mapped
    	Dim tableEntitySet As EntitySet = _
    	'   Return the table name from the storage entity set
    	Dim tableName As String = _
    		If(tableEntitySet.MetadataProperties("Table").Value, _
    	'   Find the storage property (column) that the property is mapped
    	Dim columnName As String
    	'   "Column" is a property ONLY of "ScalarPropertyMapping", which DOES NOT
    	'   work when a column is mapped to a property nested within a complex property!
    	'   Also, no object expression going from a value of "PropertyMapping" or
    	'   "ComplexPropertyMapping" seems to get me to a column name, or to a
    	'   "ScalarPropertyMapping", which does!
    	If propertyName.Contains("."c) Then
    		'   COMPLEX property -- go down chain of nested properties
    		Dim PropertyNames() As String = propertyName.Split("."c)
    		Dim PropCount As Integer = PropertyNames.Length
    		Dim Prop As EdmProperty = _
    			mapping.EntityTypeMappings.Single().Fragments.Single() _
    				.PropertyMappings.OfType(Of ComplexPropertyMapping)() _
    					.Single(Function(m) m.Property.Name = PropertyNames(0)).Property
    		For Index As Integer = 1 To PropCount - 1
    			Prop = Prop.ComplexType.Properties(PropertyNames(Index))
    		Next Index
    		columnName = Prop.PrimitiveType.Name 'gets terminal PROPERTY name, not COLUMN NAME
    		'   only works with SCALAR property, NOT one nested within a COMPLEX property
    		columnName = _
    			mapping.EntityTypeMappings.Single().Fragments.Single() _
    				.PropertyMappings.OfType(Of ScalarPropertyMapping)() _
    					.Single(Function(m) m.Property.Name = propertyName).Column.Name
    	End If
    	Return tableName.ToString & "." & columnName
    	End Function
    End Class

    How do I get the store column name corresponding to "Complex1.Scalar2" or "Complex1.Complex2.Scalar3"?! I need a solution ASAP, as I'm not fluent with metadata workspaces, and my attempts to play around with the code don't seem to be getting me anywhere.


    Robert Gustafson

    Wednesday, September 9, 2020 4:24 AM

All replies

  • Hi RobertGustafson,
    First, using complex types the default column naming convention uses underscore. 
    In order to make sure you use the complex type correctly, I suggest to follow this document in first.
    And when you work with objects that represent complex types, be aware of the following:
    1.Complex types do not have keys and therefore cannot exist independently. Complex types can only exist as properties of entity types or other complex types.
    2.Complex types cannot participate in associations and cannot contain navigation properties.
    3.Complex types cannot inherit from other complex types.
    4.You must define the complex type as a class.
    Best Regards,
    Daniel Zhang

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 10, 2020 9:55 AM