locked
I need to be able to convert between an EF model's conceptual-model info and store-model info at run-time RRS feed

  • Question

  • WHAT I HAVE:

    Visual Basic 2019, .NET 4.6.1, Entity Framework 6.4

    MY PROBLEM:

    I have been trying to write a tool to programmatically take conceptual-model info on an EF model (say, entity-name/property-name-expression) and return the equivalent store-model info (say, table-name/column-name) at run-time--on projects targeted at .NET 4.6.1. I can do the conversion when the MSL mapping file is copied to an external directory (say, the output path), but not when it's embedded in the assembly's resources. My host project has an EF model created model-first, but ideally, a conversion tool should work with any model and be independent of the way the model is set up. I've gotten the following recommendation below, but it doesn't seem to work as yet:

    The following code sample (in a class project) was done against EF 6 for getting all models and (meta data) several properties e.g. columns, is primary key etc. Don't remember which version of the Framework I've targeted but most likely 4.6.1 and should work 4.5 looking back at the code. Any ways I've only tested this with code first.

    tip provided by profile for Karen Payne on Stack Exchange

    When I make a point of incorporating EntityFramework.dll (Entity Framework 6.4) into References for the EntityCrawler.vb code I pasted, it recognizes the System.Data.Entity.Core namespace and its sub-namespaces (good), but it still gives me 6 compile-time errors: It doesn't recognize the Imports EntityFrameworkHelper.Classes nor anything dependent on that. The project features other files, but I don't know how to download the entire project from Code Developer; what's more, none of those files contain the "missing" information that the compiler is looking for. (BTW, It makes no difference at all whether the targeted platform is .NET 4.6.1 or lower, or .NET 4.7.2 or higher, so far as the sample is concerned--I've tried it at both compiler settings. In any case, I desire a solution that works on the lower platform, as some components in my project don't work correctly on the higher platform [don't ask why]. But the "code sample" above doesn't seem to have "platform versionitis"--so that's good. [Is it?])

    (BTW, is the above project actually designed to programmatically return "store equivalents" of the conceptual info? 'Cause that's what I'm after.)

    I've looked around the various EF-related namespaces; I've noted that System.Data.Entity.Core.Mappings seems to have methods and classes that may be related to getting store info from conceptual entities and properties, but it's hard to say, since online (web) documentation is scant, and I can't seem to find out where to obtain instances of these classes from an existing currently-executing EF model. I've noted the System.Data.Entity.Core.Metadata.Edm has CsdlSerialization and SsdlSerialization methods for getting XML for the CSDL (conceptual-only) and SSDL (store-only) metafiles, but no MslSerialization method for getting the XML for the conceptual-to-store mapping--which is what I want!! System.Data.Entity.Core.Common has methods for getting the DbProviderManifest/DbXmlEnabledProviderManifest info and exporting it into an XmlReader using the GetInformation method, but neither StoreSchemaMapping or StoreSchemaMappingVersion3 seem to get the conceptual-to-store XML of the MSL metafile! (It's more like XML for very rudimentary stuff instead; the use of the term "mapping" in the enums is deceptive here!)

    I know there must be a way to get the mapping data at run-time when it's stored as a resource (rather than a separate file); I just can't seem to figure out how. I know that many programs don't need to do this, but my program is different, since I need to create supplementary indexes on the store, and would prefer to stick with conceptual names so that the programmer-generated source code doesn't need to keep track of the mapping manually.

    Finally, I know this has been posted before multiple times with different wording, but I haven't gotten any recent responses to the earlier posts, so I want to be sure this--with my updated perspective--is noticed. Forgive me for being redundant, but I'm trying to put this part of my project behind me, and I know squeaky wheels are more likely to get greased.


    Robert Gustafson









    Saturday, September 5, 2020 1:06 AM

All replies

  • Couple of points

    The crawler EntityFrameworkHelper.Classes is a namespace which points to classes below. No matter it will not work against .edmx as my code is for code first with existing database.

    When looking at the xml there are three different schema definitions, this is but one of them

    http://schemas.microsoft.com/ado/2009/11/mapping/cs

    I assume you know the msl and ssdl is under obj\Debug\edmxResourcesToEmbed and of course placed into resources.

    Here is an example that reads xml, not the embedded information

    https://stackoverflow.com/questions/5313008/how-can-i-extract-the-database-table-and-column-name-for-a-property-on-an-ef4-en


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, September 5, 2020 2:10 AM
  • I got this snippet from EF6.1 Get Mapping Between Properties and Columns--converted to VB.NET--showing how to extract a table and column name from an entity and a scalar property (i.e., "AddressLine" in entity LocationSimple). What I need to know is how to modify the code to accomodate an expression for a complex property, i.e., "Address.Street.HouseNumber" in entity LocationDetail). (This code is compatible with .NET 4.6.1 [!!]; and it appears to be independent of model design [Model, Code, or Database first].)

    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 type As Type, ByVal propertyName As String, ByVal context As DbContext) As String
    			Dim metadata = 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 = metadata.GetItems(Of EntityType)(DataSpace.OSpace).Single(Function(e) objectItemCollection.GetClrType(e) Is type)
    
    			' Get the entity set that uses this entity type
    			Dim 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 = 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 = mapping.EntityTypeMappings.Single().Fragments.Single().StoreEntitySet
    
    			' Return the table name from the storage entity set
    			Dim tableName = If(tableEntitySet.MetadataProperties("Table").Value, tableEntitySet.Name)
    
    			' Find the storage property (column) that the property is mapped
    			Dim columnName = _
    				mapping.EntityTypeMappings.Single().Fragments.Single().PropertyMappings.OfType(Of ScalarPropertyMapping)().Single(Function(m) m.Property.Name = propertyName).Column.Name
    
    			Return tableName.ToString & "." & columnName
    		End Function
    	End Class
    The last line before Return seems to be where the change needs to be made. Since I'm not fluent in the rules for parsing a metadata workspace, could you show me what changes would be needed to get the column name for a complex-property expression (i.e., "Address.Street.HouseNumber"--dot-delimited from a "surface" property all the way down to an underlying scalar property)?

    Please give me an answer ASAP, as my model uses complex properties in some of its entities!

    Tuesday, September 8, 2020 3:47 AM
  • Hi RobertGustafson,
    You can try to use ToTraceString(), then parse the SQL.
    More discussion in this thread you can refer to.
    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.


    Tuesday, September 8, 2020 7:24 AM
  • Where would I use ToTraceString()?

    I think the expression mapping.EntityTypeMappings.Single().Fragments.Single().PropertyMappings is on the right track, as .OfType(Of ComplexPropertyMapping)().Single(Function(mm.Property.Name =propertyName) is also possible as a sub-expression. That can't be followed up by .Column, though, as the complex-property expression has to be carried all the way to a scalar to map to a column. What other follow-ups can get me the rest of the way? The .Property member, say? 

    (The thread you referred me to deals with finding table names, not column names! The code in my previous post already gets me the table. It needs to get the column when it corresponds not to a scalar property, but to the scalar at the end of an expression for a complex property.)

    Any code would have to be able find the column corresponding to any property expression, whether the scalar part is by itself or part of a complex-property expression (including, potentially and for the sake of argument, one with nested complex properties). I see the necessary code is being iterative or recursive in order to deal with each "part" of the property, and I just need to see how to get to mapping for the sub-elements of any complex property in order to make the code "fool-proof". (In the case of "Address.Street.HouseNumber", for instance, I'd need the code to get to the mapping for [complex] property "Address", then [complex] sub-property "Street", then finally [scalar] sub-sub-property "HouseNumber". This, of course, is just an arbitrary example; the actual code should be for a "general" solution.) 

    Don't just give me "related" threads; give me a specific solution, or at worst, a thread that clearly indicates the kind of code needed to get me there. I don't want to spend a lot of time on this particular issue--given that there's precious little detail in the docu on this stuff--so I need something specific and fast.


    Robert Gustafson








    Tuesday, September 8, 2020 7:39 AM
  • Here is a modified version of my above code. When dealing with a scalar property (i.e., "Property1"), it returns the store COLMUN name; when dealing with a property nested within a complex type (i.e., "Complex1.Property2" or "Complex1.Complex2.Property3") it returns the terminal PROPERTY name. As my comments indicate, I can't seem to get column info when the column corresponds to a primitive property nested within one or more complex properties.

    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 = _
    		mapping.EntityTypeMappings.Single().Fragments.Single().StoreEntitySet
    	'   Return the table name from the storage entity set
    	Dim tableName As String = _
    		If(tableEntitySet.MetadataProperties("Table").Value, _
    			tableEntitySet.Name).ToString
    	'   Find the storage property (column) that the property is mapped
    	Dim columnName As String
    	'   SOMEHOW I CAN'T GET A COLUMN NAME FROM A PRIMITIVE PROPERTY AT THE
    	'   END OF A COMPLEX-PROPERTY EXPRESSION!!!
    	'   "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
    	 Else
    		'   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
    
    HELP ME!!!!!!!


    Robert Gustafson

    Wednesday, September 9, 2020 3:59 AM