Mapping Read-only Entities


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=http://schemas.microsoft.com/ado/2006/04/codegeneration xmlns:edm=http://schemas.microsoft.com/ado/2006/04/edm xmlns=http://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=http://schemas.microsoft.com/ado/2006/04/edm/ssdl xmlns=http://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>


 

Comments (6)

  1. Entity Framework FAQ Version 0.1 – first draft Contents 1. Introduction . 3 1.1. Why use EDM? How does

  2. Version 0.1 – first draft, November 8, 2007 Contents 1. Introduction 1.1. About this FAQ… 1.2. Where

  3. In Part 0: Introduction of this series after asking the question &quot;Does the Entity Framework replace

  4. 江南白衣 says:

    The Entity Framework enables developers to reason about and write queries in terms of the EDM model rather than the logical schema of tables, joins, foreign keys, and so on. Many enterprise systems have multiple applications/databases with varying degrees

  5. Part of the Entity Framework FAQ . 13. EDM 13.1. Does Entity Framework support Abstract types in EDM