vb.net - How do I convert from entity/property names (conceptual) to table/column names (store)? (Trial code [in VB 2010 and EF 4] uses reflection.) -

vb.net - How do I convert from entity/property names (conceptual) to table/column names (store)? (Trial code [in VB 2010 and EF 4] uses reflection.) -

i plan add together supplementary (unique) indexes entities' tables. since can't have more 1 ef key entity, other indexes need created using constraint sql, in turns means converting between entity , property names (conceptual model) , table , column names (database store). i've taken next code person (and converted vb):

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 entityobject)() string dim type type = gettype(t) dim @ edmentitytypeattribute = getattribute(of edmentitytypeattribute)(type) homecoming at.name end function private shared function getcolumnname(of t entityobject) _ (byval propertyselector expression(of func(of t, object))) string if propertyselector nil throw new exception("""" & propertyselector.tostring & """ null.") end if dim propertyinfo propertyinfo = getpropertyinfo(propertyselector.body) dim attribute datamemberattribute = _ getattribute(of datamemberattribute)(propertyinfo) if string.isnullorempty(attribute.name) homecoming propertyinfo.name else homecoming attribute.name end if end function private shared function getattribute(of t class)(byval memberinfo memberinfo) t if memberinfo nil throw new exception("""" & memberinfo.tostring & """ null.") end if dim customattributes() object = _ memberinfo.getcustomattributes(gettype(t), false) dim attribute t = _ directcast(customattributes.where(function(a) typeof t).first(), t) homecoming attribute end function private shared function getpropertyinfo(byval propertyselector expression) propertyinfo if propertyselector nil throw new exception("""" & propertyselector.tostring & """ null.") end if dim memberexpression memberexpression = _ trycast(propertyselector, memberexpression) if memberexpression nil dim unaryexpression unaryexpression = _ trycast(propertyselector, unaryexpression) if unaryexpression isnot nil _ andalso unaryexpression.nodetype = expressiontype.convert memberexpression = trycast(unaryexpression.operand, memberexpression) end if end if if memberexpression isnot nil _ andalso memberexpression.member.membertype = membertypes.property homecoming directcast(memberexpression.member, propertyinfo) else throw new argumentexception("no property reference found.", "propertyselector") end if end function ' invocation illustration public shared sub test() dim table string = gettablename(of entity)() dim column string = getcolumnname(of entity)(function(entity) entity.property) end sub end class

i need know things:

i don't need objectcontext code, i? what if property need convert field part of complex property? need augment or modify code such situation? (i.e., suppose have entity called location, complex property of address, , need field address.street. use, columnname = getcolumnname(of location) (function(location) location.address.street), or have more?) suppose code doesn't know beforehand names of entity , property. there way adapt code take string value each (at to the lowest degree property name; i.e., "entity.property"), find appropriate class/property reference therefrom?

please reply these questions asap!

here's general-purpose algorithm converting between conceptual , store information, written in visual basic 2010.

i have written new routine convert entity/property pair table/column pair. class, mslmappingaction, takes in constructor model name , either xelement xml tree, msl mapping file, or xml string. 1 uses conceptualtostore method take string specifying entity , property "expressions" (stored in mslconceptualinfo structure) , find table , column names (stored in mslstoreinfo structure).

notes:

one write "storetoconceptual" method convert in other direction, xml queries bit more complex. same goes handling navigation-propery/function/stored-procedure mappings. beware inherited properties of derived entities! if property not specific derived entity, should utilize base of operations entity's name.)

here's code.

host code: (for xml sample given [see bottom], returns table name "locations" , column name "address_street" store info when given entity "location" , property look "address.street" [and conceptual model name "sctmodel"]):

dim msl mslmappingaction = new mslmappingaction(".\sctmodel.msl", "sctmodel") dim conceptualinfo mslconceptualinfo = new mslconceptualinfo {.entityname = "location", .propertyname = "address.street"} dim storeinfo mslstoreinfo = msl.conceptualtostore(conceptualinfo) messagebox.show(storeinfo.tablename & ": " & storeinfo.columnname)

class code:

option infer on imports system.xml.linq ''' <summary> ''' class allows 1 convert between ef conceptual model's entity/property pair ''' , database store's table/column pair. ''' </summary> ''' <remarks>it takes business relationship entity splitting , complex-property designations; ''' not take business relationship inherited properties ''' (in such case, should access entity's base of operations class)</remarks> public class mslmappingaction ' private fields , routines private mmamslmapping xelement private mmamodelname, mmanamespace string private function fullelementname(byval elementname string) string ' pre-pend namespace elementname homecoming "{" & mmanamespace & "}" & elementname end function private sub validateparams(byval mappingxml xelement, byval modelname string) ' verify model name specified if string.isnullorempty(modelname) throw new entityexception("entity model name not given!") end if ' verify we're using c-s space if mappingxml.@space <> "c-s" throw new metadataexception("xml not c-s mapping data!") end if ' namespace , set private variables mmanamespace = mappingxml.@xmlns mmamslmapping = mappingxml : mmamodelname = modelname end sub private function issequenceempty(items ienumerable(of xelement)) boolean ' determine if query result empty homecoming _ items nil orelse items.count = 0 end function ' properties ''' <summary> ''' name of conceptual entity model ''' </summary> ''' <returns>conceptual-model string</returns> ''' <remarks>model name can set in constructor</remarks> public readonly property entitymodelname() string homecoming mmamodelname end end property ''' <summary> ''' name of mapping namespace ''' </summary> ''' <returns>namespace string of c-s mapping layer</returns> ''' <remarks>this value determined when xml mapping ''' first parsed in constructor</remarks> public readonly property mappingnamespace() string homecoming mmanamespace end end property ' constructors ''' <summary> ''' c-s mapping info 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 xelement, byval modelname string) validateparams(mappingxml, modelname) end sub ''' <summary> ''' c-s mapping info 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 string, byval modelname string) dim mappingxml xelement = xelement.load(mslfile) validateparams(mappingxml, modelname) end sub ' methods ''' <summary> ''' c-s mapping infomration 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 string, byval modelname string) homecoming new mslmappingaction(xelement.parse(xmlstring), modelname) end function ''' <summary> ''' convert conceptual entity/property info store table/column info ''' </summary> ''' <param name="conceptualinfo">conceptual-model info ''' (.entityname = entity look string, .propertyname = property look string)</param> ''' <returns>store info (.tablename = table-name string, .columnname = column-name string)</returns> ''' <remarks></remarks> public function conceptualtostore(byval conceptualinfo mslconceptualinfo) mslstoreinfo dim storeinfo new mslstoreinfo conceptualinfo ' prepare query xml if not .entityname.contains(".") ' create sure entity name qualified .entityname = mmamodelname & "." & .entityname end if ' separate property names if there's complex-type nesting dim properties() string = .propertyname.split(".") ' relevant entity mapping dim mappinginfo ienumerable(of xelement) = _ (from mi in mmamslmapping.descendants(fullelementname("entitytypemapping")) _ mi.@typename = "istypeof(" & .entityname & ")" _ orelse mi.@typename = .entityname _ select mi) ' create sure entity in model if issequenceempty(mappinginfo) throw new entityexception("entity """ & .entityname & """ not found!") end if ' mapping fragments dim mappingfragments ienumerable(of xelement) = _ (from mf in mappinginfo.descendants(fullelementname("mappingfragment")) _ select mf) ' create sure there's @ to the lowest degree 1 fragment if issequenceempty(mappingfragments) throw new entityexception("entity """ & .entityname & """ not mapped!") end if ' search each mapping fragment desired property each mappingfragment in mappingfragments ' physical table fragment storeinfo.tablename = mappingfragment.@storeentityset ' search property look chain dim propertymapping ienumerable(of xelement) = {mappingfragment} ' parse complex property info (if any) index = 0 ubound(properties) - 1 ' go downwards 1 level dim complexpropertyname = properties(index) propertymapping = _ (from pm in propertymapping.elements(fullelementname("complexproperty")) _ pm.@name = complexpropertyname) ' verify property specified level exists if issequenceempty(propertymapping) exit 'go next fragment if not end if next index ' property not found? seek next fragment if issequenceempty(propertymapping) go on end if ' parse scalar property info dim scalarpropertyname = properties(ubound(properties)) dim columnname string = _ (from pm in propertymapping.elements(fullelementname("scalarproperty")) _ pm.@name = scalarpropertyname _ select cn = pm.@columnname).firstordefault ' verify scalar property exists if not string.isnullorempty(columnname) ' yes? homecoming (exit) column info storeinfo.columnname = columnname : homecoming storeinfo end if next mappingfragment ' property wasn't found throw new entityexception("property """ & .propertyname _ & """ of entity """ & .entityname & """ not found!") end end function end class ''' <summary> ''' conceptual-model entity , property info ''' </summary> public construction mslconceptualinfo ''' <summary> ''' name of entity in conceptual model ''' </summary> ''' <value>entity look string</value> ''' <remarks>entityname may or may not qualified (i.e., "modelname.entityname"); ''' when mapping method called mslmappingaction class, conceptual model's ''' name , period pre-pended if it's omitted</remarks> public property entityname string ''' <summary> ''' name of property in entity ''' </summary> ''' <value>property look string</value> ''' <remarks>propertyname may either stand-alone scalar property or scalar property ''' within 1 or more levels of complex-type properties; in latter case, must ''' qualified (i.e., "complexpropertyname.innercomplexpropertyname.scalarpropertyname")</remarks> public property propertyname string end construction ''' <summary> ''' database-store table , column info ''' </summary> public construction mslstoreinfo ''' <summary> ''' name of table in database ''' </summary> public property tablename string ''' <summary> ''' name of column in database table ''' </summary> public property columnname string end construction

the grab node names have have namespace prepended them. tripped me until checked elements 1 @ time!

here's sample xml -- load ".\sctmodel.msl" file in 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>

vb.net visual-studio-2010 linq reflection entity-framework-4