How to Do Custom Mapping Using Entity SQL Views

  • [アーティクル]

This post was intended to be a reply to this question https://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="https://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="https://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!

Comments