How to Do Custom Mapping Using Entity SQL Views


This post was intended to be a reply to this question http://forums.microsoft.com/msdn/showpost.aspx?postid=2440161&siteid=1 on the Entity Framework forum, but its size exceeds the forum server limit. Besides, it’s useful in general. So I decided to post it here. In this post you’ll find an entire repro of the subject scenario from the forum.


 


Create the following database schema:


 



create table Organization


(


      OrganizationId int primary key,


      OrganizationName nvarchar(100)


);


 


create table OrganizationRelationship


(


      OrganizationA int foreign key references Organization(OrganizationId),


      OrganizationB int foreign key references Organization(OrganizationId),


      RelationshipId int


      primary key (OrganizationA, OrganizationB, RelationshipId)


);


 


insert into Organization values(1, ‘One’);


insert into Organization values(2, ‘Two’);


insert into Organization values(3, ‘Three’);


insert into Organization values(4, ‘Four’);


 


— 1: A partner B


— 2: A client B


— 3: A subsidiary B


insert into OrganizationRelationship values(1, 2, 1);


insert into OrganizationRelationship values(3, 4, 1);


insert into OrganizationRelationship values(2, 1, 1);


insert into OrganizationRelationship values(4, 3, 1);


insert into OrganizationRelationship values(1, 3, 2);


insert into OrganizationRelationship values(2, 4, 2);


insert into OrganizationRelationship values(1, 4, 3);


insert into OrganizationRelationship values(2, 3, 3);


 


 


I ran EdmGen.exe to do a first cut of a model, which I hacked manually afterwards.


CSDL remains virtually unchanged (except some renaming). Notice that it represents the way things are in the database, i.e. the associations match the foreign keys between the two tables:


 



<?xml version=1.0 encoding=utf-8?>


<Schema Namespace=Organizations.Store Alias=Self ProviderManifestToken=09.00.3054 xmlns=http://schemas.microsoft.com/ado/2006/04/edm/ssdl>


  <EntityContainer Name=dbo>


    <EntitySet Name=Organization EntityType=Organizations.Store.Organization />


    <EntitySet Name=OrganizationRelationship EntityType=Organizations.Store.OrganizationRelationship />


    <AssociationSet Name=OrganizationRelationshipA Association=Organizations.Store.OrganizationRelationshipA>


      <End Role=Organization EntitySet=Organization />


      <End Role=OrganizationRelationship EntitySet=OrganizationRelationship />


    </AssociationSet>


    <AssociationSet Name=OrganizationRelationshipB Association=Organizations.Store.OrganizationRelationshipB>


      <End Role=Organization EntitySet=Organization />


      <End Role=OrganizationRelationship EntitySet=OrganizationRelationship />


    </AssociationSet>


  </EntityContainer>


  <EntityType Name=Organization>


    <Key>


      <PropertyRef Name=OrganizationId />


    </Key>


    <Property Name=OrganizationId Type=int Nullable=false />


    <Property Name=OrganizationName Type=nvarchar MaxLength=100 />


  </EntityType>


  <EntityType Name=OrganizationRelationship>


    <Key>


      <PropertyRef Name=OrganizationA />


      <PropertyRef Name=OrganizationB />


      <PropertyRef Name=RelationshipId />


    </Key>


    <Property Name=OrganizationA Type=int Nullable=false />


    <Property Name=OrganizationB Type=int Nullable=false />


    <Property Name=RelationshipId Type=int Nullable=false />


  </EntityType>


  <Association Name=OrganizationRelationshipA>


    <End Role=Organization Type=Organizations.Store.Organization Multiplicity=1 />


    <End Role=OrganizationRelationship Type=Organizations.Store.OrganizationRelationship Multiplicity=* />


    <ReferentialConstraint>


      <Principal Role=Organization>


        <PropertyRef Name=OrganizationId />


      </Principal>


      <Dependent Role=OrganizationRelationship>


        <PropertyRef Name=OrganizationA />


      </Dependent>


    </ReferentialConstraint>


  </Association>


  <Association Name=OrganizationRelationshipB>


    <End Role=Organization Type=Organizations.Store.Organization Multiplicity=1 />


    <End Role=OrganizationRelationship Type=Organizations.Store.OrganizationRelationship Multiplicity=* />


    <ReferentialConstraint>


      <Principal Role=Organization>


        <PropertyRef Name=OrganizationId />


      </Principal>


      <Dependent Role=OrganizationRelationship>


        <PropertyRef Name=OrganizationB />


      </Dependent>


    </ReferentialConstraint>


  </Association>


</Schema>


 


 


What you have to notice in the MSL is that if you provide a QueryView for one set, all related sets must be given a QueryView. Those are easy to write once you have one. Yesterday I wrote a more complex view than it was need. Also notice that the original associations from SSDL are missing here. That’s because they don’t make sense in the model. Instead, we introduce new associations – Partner, Client, etc. The OrganizationRelationship entity set has also disappeared for the same reason.


 



<?xml version=1.0 encoding=utf-8?>


<Mapping Space=C-S xmlns=urn:schemas-microsoft-com:windows:storage:mapping:CS>


  <EntityContainerMapping StorageEntityContainer=dbo CdmEntityContainer=Organizations>


    <EntitySetMapping Name=Organization>


      <QueryView>


        SELECT VALUE Organizations.Organization(org.OrganizationId, org.OrganizationName)


        FROM dbo.Organization AS org;


      </QueryView>


    </EntitySetMapping>


    <AssociationSetMapping Name=Partner>


      <QueryView>


        SELECT VALUE Organizations.Partner(


                         CREATEREF(Organizations.Organization, ROW(rel.OrganizationA)),


                         CREATEREF(Organizations.Organization, ROW(rel.OrganizationB)) )


        FROM dbo.OrganizationRelationship AS rel


        WHERE rel.RelationshipId = 1;


      </QueryView>


    </AssociationSetMapping>


    <AssociationSetMapping Name=Client>


      <QueryView>


        SELECT VALUE Organizations.Client(


                         CREATEREF(Organizations.Organization, ROW(rel.OrganizationA)),


                         CREATEREF(Organizations.Organization, ROW(rel.OrganizationB)) )


        FROM dbo.OrganizationRelationship AS rel


        WHERE rel.RelationshipId = 2;


      </QueryView>


    </AssociationSetMapping>


  </EntityContainerMapping>


</Mapping>


 


 


In CSDL we model the sets and types that we already saw in MSL. Additionally, we create navigation properties for each kind of relationship:


 



<?xml version=1.0 encoding=utf-8?>


<Schema Namespace=Organizations Alias=Self xmlns=http://schemas.microsoft.com/ado/2006/04/edm>


  <EntityContainer Name=Organizations>


    <EntitySet Name=Organization EntityType=Organizations.Organization />


    <AssociationSet Name=Partner Association=Organizations.Partner>


      <End Role=OrganizationA EntitySet=Organization />


      <End Role=OrganizationB EntitySet=Organization />


    </AssociationSet>


    <AssociationSet Name=Client Association=Organizations.Client>


      <End Role=OrganizationA EntitySet=Organization />


      <End Role=OrganizationB EntitySet=Organization />


    </AssociationSet>


  </EntityContainer>


  <EntityType Name=Organization>


    <Key>


      <PropertyRef Name=OrganizationId />


    </Key>


    <Property Name=OrganizationId Type=Int32 Nullable=false />


    <Property Name=OrganizationName Type=String MaxLength=100 />


    <NavigationProperty Name=Partners Relationship=Organizations.Partner FromRole=OrganizationA ToRole=OrganizationB />


    <NavigationProperty Name=Clients Relationship=Organizations.Client FromRole=OrganizationA ToRole=OrganizationB />


  </EntityType>


  <Association Name=Partner>


    <End Role=OrganizationA Type=Organizations.Organization Multiplicity=1 />


    <End Role=OrganizationB Type=Organizations.Organization Multiplicity=* />


  </Association>


  <Association Name=Client>


    <End Role=OrganizationA Type=Organizations.Organization Multiplicity=1 />


    <End Role=OrganizationB Type=Organizations.Organization Multiplicity=* />


  </Association>


</Schema>


 


 


To verify the model I ran the following Entity SQL query:


 



SELECT org, org.Partners, org.Clients


FROM Organizations.Organization AS org;


— Powered by eSqlBlast


 


Which produced the following correct result:


 
























org


Partners


Clients










OrganizationId


OrganizationName


1


One










OrganizationId


OrganizationName


2


Two










OrganizationId


OrganizationName


3


Three










OrganizationId


OrganizationName


2


Two










OrganizationId


OrganizationName


1


One










OrganizationId


OrganizationName


4


Four










OrganizationId


OrganizationName


3


Three










OrganizationId


OrganizationName


4


Four














OrganizationId


OrganizationName


4


Four










OrganizationId


OrganizationName


3


Three






 


 


I’m leaving it up to you to expose the Subsidiaries navigation property for homework.


Enjoy!


 

Skip to main content