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