Mapping Read-only Entities

  • Article

I can't take credit for the description of how to do this, but a very useful example was recently posted to the forum, and I wanted to make sure more people saw this/it is easy to find in the future.  Also, Julie Lerman was mentioning to me today that there's a lot of good content in the forums which isn't as accessible as it should be so we ought to post more of these kinds of things to the blogs--feedback I intend to take to heart.  So, here's the first installment on that new strategy:

From Srikanth Mandadi:

Mapping Read-only Entities

One of the options is to specify the mapping using QueryView elements in MSL file. Using this feature, you can describe the mapping for an EntitySet in Edm space using an Entity Sql query. The designer does not support this feature. So you might have to hand write the schema files.

 

Using QueryView elements currently has some restrictions:

  1. If you specify a mapping for an EntitySet using a QueryView, you would have to map all the connected EntitySets and AssociationSets in the sub-graph using a QueryView.
  2. You can not use all operators in Query Views. Some of the operators you can use are : Project, Join, Union, Case etc.

Here's a simple set of schemas that shows how to use this feature. These should work with Northwind DB. MSL file which is at the end is the most interesting for this scenario. 

 

CSDL

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

<Schema Namespace="Microsoft.CDP.Samples.Northwind" Alias="Self" xmlns:cg="schemas.microsoft.com/ado/2006/04/codegeneration" xmlns:edm="schemas.microsoft.com/ado/2006/04/edm" xmlns="schemas.microsoft.com/ado/2006/04/edm">

<EntityContainer Name="Microsoft_CDP_Samples_Northwind_NorthwindContainer">

<Documentation>

<Summary>Summary: Entity Container for storing Northwind instances</Summary>

<LongDescription>LongDescription: This Entity Container is for storing Northwind instances</LongDescription>

</Documentation>

<!-- EntitySet definitions -->

<EntitySet Name="Products" EntityType="Self.Product">

<Documentation>

<Summary>EntitySet Products is for storing instances of EntityType Product</Summary>

<LongDescription>This EntitySet having name Products is for storing instances of EntityType Product</LongDescription>

</Documentation>

</EntitySet>

<EntitySet Name="Categories" EntityType="Self.Category">

<Documentation>

<Summary>EntitySet Categories is for storing instances of EntityType Category</Summary>

</Documentation>

</EntitySet>

<!-- RelationshipSet for customers -->

<AssociationSet Name="CategoryProducts" Association="Self.CategoryProduct">

<Documentation>

<Summary>AssociationSet CategoryProducts is for storing instances of Association CategoryProduct</Summary>

<LongDescription>This AssociationSet having name=CategoryProducts is for storing instances of Association CategoryProduct</LongDescription>

</Documentation>

<End Role="Category" EntitySet="Categories">

<Documentation>

<Summary>This end of the relaitonship-instance describes the Category role for AssociationSet CategoryProducts</Summary>

</Documentation>

</End>

<End Role="Product" EntitySet="Products">

<Documentation>

<LongDescription>This end of the relaitonship-instance describes the Product role for AssociationSet CategoryProducts</LongDescription>

</Documentation>

</End>

</AssociationSet>

</EntityContainer>

<EntityType Name="Product">

<Documentation>

<Summary>Summary: EntityType named Product describes the content model for Product</Summary>

<LongDescription>LongDescription: The EntityType named Product describes the content model for Product</LongDescription>

</Documentation>

<Key>

<PropertyRef Name="ProductID" />

</Key>

<Property Name="ProductID" Type="Int32" Nullable="false">

<Documentation>

<Summary>Summary: This is the key property of EntityType Product</Summary>

<LongDescription>LongDescription: This is the key property of EntityType Product</LongDescription>

</Documentation>

</Property>

<Property Name="ProductName" Type="String" MaxLength="40">

<Documentation>

<Summary>Summary: This property describes the name of the Product</Summary>

</Documentation>

</Property>

<Property Name="QuantityPerUnit" Type="String" MaxLength="20">

<Documentation>

<LongDescription>LongDescription: This property describes the quantity per unit corresponding to a product</LongDescription>

</Documentation>

</Property>

<Property Name="UnitPrice" Type="Decimal" Precision="28" Scale="4" />

<Property Name="UnitsInStock" Type="Int16" />

<Property Name="UnitsOnOrder" Type="Int16" />

<Property Name="ReorderLevel" Type="Int16" />

<NavigationProperty Name="Category" Relationship="Self.CategoryProduct" FromRole="Product" ToRole="Category">

<Documentation>

<Summary>This navigation property allows for traversing to Product-instances associated with a Category-instance</Summary>

<LongDescription> </LongDescription>

</Documentation>

</NavigationProperty>

</EntityType>

<EntityType Name="Category">

<Documentation>

<Summary></Summary>

<LongDescription>LongDescription: The EntityType named Category describes the content model for Category</LongDescription>

</Documentation>

<Key>

<PropertyRef Name="CategoryID" />

</Key>

<Property Name="CategoryID" Type="Int32" Nullable="false" />

<Property Name="CategoryName" Type="String" MaxLength="15" />

<Property Name="CategoryDescription" Type="String" MaxLength="Max" />

<NavigationProperty Name="Products" Relationship="Self.CategoryProduct" FromRole="Category" ToRole="Product" />

</EntityType>

<EntityType Name="DiscontinuedProduct" BaseType="Self.Product">

<Documentation>

<Summary>Summary: EntityType named DiscontinuedProduct describes the content model for Product</Summary>

</Documentation>

</EntityType>

<Association Name="CategoryProduct">

<Documentation>

<Summary>Association CategoryProduct describes the participating end of the CategoryProduct relationship</Summary>

</Documentation>

<End Role="Category" Type="Self.Category" Multiplicity="1">

<Documentation>

<Summary>This end of the relationship-instance describes the Category role for Association CategoryProduct</Summary>

</Documentation>

</End>

<End Role="Product" Type="Self.Product" Multiplicity="*">

<Documentation>

<LongDescription>This end of the relationship-instance describes the Product role for Association CategoryProduct</LongDescription>

</Documentation>

</End>

</Association>

</Schema>

 

SSDL File :

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

<Schema Namespace="Northwind" Alias="Self" ProviderManifestToken="09.00" xmlns:edm="schemas.microsoft.com/ado/2006/04/edm/ssdl" xmlns="schemas.microsoft.com/ado/2006/04/edm/ssdl">

  <EntityContainer Name="dbo">

    <Documentation>

      <Summary>Entity Container for storing dbo instances</Summary>

      <LongDescription>This Entity Container is for storing dbo instances</LongDescription>

    </Documentation>

    <EntitySet Name="Products" EntityType="Self.Product" Schema="dbo" Table="Products">

      <Documentation>

        <Summary>EntitySet Products is for storing instances of EntityType Product</Summary>

        <LongDescription>This EntitySet having name Products is for storing instances of EntityType Product</LongDescription>

    </Documentation>

    </EntitySet>

    <EntitySet Name="Categories" EntityType="Self.Category" Schema="dbo" Table="Categories" />

    <AssociationSet Name="CategoryProducts" Association="Self.CategoryProduct">

      <End Role="Category" EntitySet="Categories" />

      <End Role="Product" EntitySet="Products" />

    </AssociationSet>

  </EntityContainer>

  <EntityType Name="Product">

    <Documentation>

      <Summary>EntityType named Product describes the content model for Product</Summary>

      <LongDescription>The EntityType named Product describes the content model for Product</LongDescription>

    </Documentation>

    <Key>

      <PropertyRef Name="ProductID" />

    </Key>

    <Property Name="ProductID" Type="int" Nullable="false" />

    <Property Name="CategoryID" Type="int" />

    <Property Name="ProductName" Type="nvarchar" MaxLength="40" />

    <Property Name="QuantityPerUnit" Type="nvarchar" MaxLength="20" />

    <Property Name="UnitPrice" Type="decimal" Precision="28" Scale="4" />

    <Property Name="UnitsInStock" Type="smallint" />

    <Property Name="UnitsOnOrder" Type="smallint" />

    <Property Name="ReorderLevel" Type="smallint" />

    <Property Name="Discontinued" Type="bit" />

  </EntityType>

  <EntityType Name="Category">

    <Key>

      <PropertyRef Name="CategoryID" />

    </Key>

    <Property Name="CategoryID" Type="int" Nullable="false" />

    <Property Name="CategoryName" Type="nvarchar" MaxLength="15" />

    <Property Name="Description" Type="nvarchar(max)" />

  </EntityType>

  <Association Name="CategoryProduct">

    <End Role="Category" Type="Self.Category" Multiplicity="0..1" />

    <End Role="Product" Type="Self.Product" Multiplicity="*" />

    <ReferentialConstraint>

      <Principal Role="Category">

        <PropertyRef Name="CategoryID" />

      </Principal>

      <Dependent Role="Product">

        <PropertyRef Name="CategoryID" />

      </Dependent>

    </ReferentialConstraint>

  </Association>

  <Function Name="SUBSTRING" ReturnType="varchar" BuiltIn="true">

    <Documentation>

      <Summary>This function accepts a source string, the starting position and the length of the sub-string to be extracted</Summary>

      <LongDescription>This is a long Description. This function accepts a source string, the starting position and the length of the sub-string to be extracted</LongDescription>

    </Documentation>

    <Parameter Name="str" Mode="In" Type="varchar" />

    <Parameter Name="start" Mode="In" Type="int">

      <Documentation>

        <Summary>The starting position of the substring</Summary>

        <LongDescription>This is a long Description. The starting position of the substring</LongDescription>

      </Documentation>

    </Parameter>

    <Parameter Name="length" Mode="In" Type="int" />

  </Function>

</Schema>

MSL File :

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

<Mapping xmlns="urn:

schemas-microsoft-com:windows:

storage:mapping:CS" Space="C-S">

  <Alias Key="CNorthwind" Value="Microsoft.CDP.Samples.Northwind" />

  <Alias Key="SNorthwind" Value="Northwind" />

  <EntityContainerMapping CdmEntityContainer="Microsoft_CDP_Samples_Northwind_NorthwindContainer" StorageEntityContainer="dbo">

    <EntitySetMapping Name="Categories">

      <QueryView>

        SELECT VALUE Microsoft.CDP.Samples.Northwind.Category(C.CategoryID, C.CategoryName, C.Description)

        FROM dbo.Categories as C

      </QueryView>

    </EntitySetMapping>

    <EntitySetMapping Name="Products">

      <QueryView>

        SELECT VALUE

        CASE

        WHEN P.Discontinued = False THEN Microsoft.CDP.Samples.Northwind.Product(P.ProductID, P.ProductName, P.QuantityPerUnit, P.UnitPrice, P.UnitsInStock, P.UnitsOnOrder, P.ReorderLevel)

        ELSE Microsoft.CDP.Samples.Northwind.DiscontinuedProduct(P.ProductID, P.ProductName, P.QuantityPerUnit, P.UnitPrice, P.UnitsInStock, P.UnitsOnOrder, P.ReorderLevel)

        END

        FROM dbo.Products AS P WHERE P.Discontinued IN {True, False}

      </QueryView>

      <QueryView TypeName="CNorthwind.Product">

  SELECT VALUE

        Microsoft.CDP.Samples.Northwind.Product(P.ProductID, "FooProduct", P.QuantityPerUnit, P.UnitPrice, P.UnitsInStock, P.UnitsOnOrder, P.ReorderLevel)

        FROM dbo.Products AS P WHERE P.Discontinued = False

      </QueryView>

   </EntitySetMapping>

    <AssociationSetMapping Name="CategoryProducts">

      <QueryView>

        SELECT VALUE Microsoft.CDP.Samples.Northwind.CategoryProduct(CP.CategoryRef, CP.ProductRef)

        FROM (

        SELECT

        CreateRef(Microsoft_CDP_Samples_Northwind_NorthwindContainer.Categories,

        row(P.CategoryID),Microsoft.CDP.Samples.Northwind.Category) AS CategoryRef,

        CreateRef(Microsoft_CDP_Samples_Northwind_NorthwindContainer.Products,

        row(P.ProductID),Microsoft.CDP.Samples.Northwind.Product) AS ProductRef

        FROM dbo.Products AS P) AS CP

      </QueryView>

    </AssociationSetMapping>

  </EntityContainerMapping>

</Mapping>