none
Parent-child entity set RRS feed

  • Question


  • Hello everyone,

    Suppose in the database I have a parent-child relationship between let's say Products and Categories i.e. in Products I have categoryID reference to Categories.ID.

    Can I model these two tables as a single entity so I would have in the same entity:

    id (the product id)
    name (product name)
    categoryid (from the Categories table, the parent row associated with the product id)
    categoryname (from the Categories table)


    Please help,
    Thanks
    Wednesday, February 27, 2008 12:09 PM

Answers

  • Hello,

    I think the best in your case is to use a ssdl view.

     

    In csdl, you should add this:

    Code Snippet
    <EntitySet Name="ProductsWithCategory" EntityType="NorthwindEFModel.ProductWithCategory" />

    and this:

    Code Snippet
    <EntityType Name="ProductWithCategory">
      <Key>
        <PropertyRef Name="ProductID" />
      </Key>
      <Property Name="ProductID" Type="Int32" Nullable="false" />
      <Property Name="ProductName" Type="String" Nullable="false" MaxLength="40" />
      <Property Name="CategoryID" Type="Int32" Nullable="false" />
      <Property Name="CategoryName" Type="String" Nullable="false" MaxLength="15" />
    </EntityType>

     

    In ssdl, you should add this:

    Code Snippet
    <EntitySet Name="ProductsWithCategory" EntityType="NorthwindEFModel.Store.ProductsWithCategory">
      <DefiningQuery>
        SELECT P.ProductID, P.ProductName, C.CategoryID, C.CategoryName
        FROM Products AS P
        LEFT OUTER JOIN Categories AS C ON C.CategoryID = P.CategoryID
      </DefiningQuery>
    </EntitySet>

    and this:

    Code Snippet
    <EntityType Name="ProductsWithCategory">
      <Key>
        <PropertyRef Name="ProductID" />
      </Key>
      <Property Name="ProductID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
      <Property Name="ProductName" Type="nvarchar" Nullable="false" MaxLength="40" />
      <Property Name="CategoryID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
      <Property Name="CategoryName" Type="nvarchar" Nullable="false" MaxLength="15" />
    </EntityType>

     

    In msl, you map these :

    Code Snippet
    <EntitySetMapping Name="ProductsWithCategory">
      <EntityTypeMapping TypeName="IsTypeOf(NorthwindEFModel.ProductWithCategory)">
        <MappingFragment StoreEntitySet="ProductsWithCategory">
          <ScalarProperty Name="CategoryName" ColumnName="CategoryName" />
          <ScalarProperty Name="CategoryID" ColumnName="CategoryID" />
          <ScalarProperty Name="ProductName" ColumnName="ProductName" />
          <ScalarProperty Name="ProductID" ColumnName="ProductID" />
        </MappingFragment>
      </EntityTypeMapping>
    </EntitySetMapping>

     

     

    Wednesday, February 27, 2008 1:34 PM

All replies

  • Hello,

    I think the best in your case is to use a ssdl view.

     

    In csdl, you should add this:

    Code Snippet
    <EntitySet Name="ProductsWithCategory" EntityType="NorthwindEFModel.ProductWithCategory" />

    and this:

    Code Snippet
    <EntityType Name="ProductWithCategory">
      <Key>
        <PropertyRef Name="ProductID" />
      </Key>
      <Property Name="ProductID" Type="Int32" Nullable="false" />
      <Property Name="ProductName" Type="String" Nullable="false" MaxLength="40" />
      <Property Name="CategoryID" Type="Int32" Nullable="false" />
      <Property Name="CategoryName" Type="String" Nullable="false" MaxLength="15" />
    </EntityType>

     

    In ssdl, you should add this:

    Code Snippet
    <EntitySet Name="ProductsWithCategory" EntityType="NorthwindEFModel.Store.ProductsWithCategory">
      <DefiningQuery>
        SELECT P.ProductID, P.ProductName, C.CategoryID, C.CategoryName
        FROM Products AS P
        LEFT OUTER JOIN Categories AS C ON C.CategoryID = P.CategoryID
      </DefiningQuery>
    </EntitySet>

    and this:

    Code Snippet
    <EntityType Name="ProductsWithCategory">
      <Key>
        <PropertyRef Name="ProductID" />
      </Key>
      <Property Name="ProductID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
      <Property Name="ProductName" Type="nvarchar" Nullable="false" MaxLength="40" />
      <Property Name="CategoryID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
      <Property Name="CategoryName" Type="nvarchar" Nullable="false" MaxLength="15" />
    </EntityType>

     

    In msl, you map these :

    Code Snippet
    <EntitySetMapping Name="ProductsWithCategory">
      <EntityTypeMapping TypeName="IsTypeOf(NorthwindEFModel.ProductWithCategory)">
        <MappingFragment StoreEntitySet="ProductsWithCategory">
          <ScalarProperty Name="CategoryName" ColumnName="CategoryName" />
          <ScalarProperty Name="CategoryID" ColumnName="CategoryID" />
          <ScalarProperty Name="ProductName" ColumnName="ProductName" />
          <ScalarProperty Name="ProductID" ColumnName="ProductID" />
        </MappingFragment>
      </EntityTypeMapping>
    </EntitySetMapping>

     

     

    Wednesday, February 27, 2008 1:34 PM

  • Cool, thanks!
    In this case linq over the ProductWithCategory table will result to a single query i.e. suppose I want to return the products that are called 'Computer' does it know to modify the underlying query to

    SELECT P.ProductID, P.ProductName, C.CategoryID, C.CategoryName
        FROM Products AS P
        LEFT OUTER JOIN Categories AS C ON C.CategoryID = P.CategoryID


    WHERE p.ProductName = 'Computer'

    ?

    Thanks again,
    Andrei
    Wednesday, February 27, 2008 2:12 PM
  • In fact, you have two possibilities. You want to filter it directly on the model or not.

    If you want to filter it directly on the model, you can change your SQL request or, probably better, use a mapping condition.

    Else, you can simply use a LINQ Query with where:

    Code Snippet
    using (var context = new NorthwindEFEntities())
    {
        foreach (var c in from pwc in context.ProductsWithCategory where pwc.ProductName == "Computer" select pwc.CategoryName)
            Console.WriteLine(c);
    }

     

     

     

     

    Wednesday, February 27, 2008 2:29 PM
  • While all this will work, do note that the resulting entity will be read-only.

     

    More importantly, right now, the update model from database wizard completely regenerates the SSDL section of your edmx file, so if you use this feature, you will lose your defining queries. (We would like to fix this issue but may not be able to get to it.)

     

    So, an option would be to use QueryViews in your MSL. Julie Lerman has a good article about the difference between DefiningQueries and QueryViews here:

    http://www.thedatafarm.com/blog/2007/11/02/EDMQueryViewsVsDefiningQueriesAndReadonlyViews.aspx

    Thursday, February 28, 2008 1:38 AM
    Moderator

  • "do note that the resulting entity will be read-only."

    Oh great - so much for the "splitting entities across multiple tables" "feature".

    Is there any reason then why to use entity framework and not Linq to Sql ? Especially that the sql code generated by ADO.NET EF is sometimes really C R A P ...


    Thanks for your answers - I've made up my mind about ADO.NET EF now Smile
    Thursday, February 28, 2008 8:46 AM
  • Yes there are.

    I think that the two most important reasons are these:

    • With EF, you can really have a relational conception for your DB and an object conception for your entities. Too often, I see in project a copy between two parts. With LINQ To SQL, you only do "SQL object".  Moreover, EDM will ignore intermediate table on many to many relationship.
    • With EF, if the DBA wants to change tables structure for optimize it, with LINQ To SQL, the generated code will change and so your code too. With EDM, you can often change only your mapping. So you will only change ssdl and msl but NOT csdl. So generated entities won't change and so your can't won't change too.

    I think it's largely efficient to prefer EF than LINQ To SQL

    Thursday, February 28, 2008 9:29 AM
  • Also keep in mind that while the entityset will be read-only when you use defining queries or the like--that just means that the EF can't automatically generate the updates for you.  You can, however, define and map stored procedures for these sceanrios, and then updates will work fine.

     

    The updates are exactly where the dilemma is.  In a number of these kinds of mapping scenarios, the model and the database are not really able to guarantee that every bit of data can be round tripped.  Sometimes with extra knowledge of your app you can make a rule such that round-tripping will work, but the way you have to represent that rule is in the code in your stored procedures for the updates.

     

    - Danny

    Thursday, February 28, 2008 4:25 PM