none
Shouldn't Relations Automatically Generate Sub-Collections? RRS feed

  • Question

  • In ScottGu's blog he mentioned this:

    When dragging two tables into the designer with a relation, like "Car" which has a foreigh key (FK) to "CarStyle", then a "sub-collection" would automatically be generated.

    So we could access like this:
        db.Cars.CarStyles.Name

    Yet when carefully adding adding such an FK, all the normal collections are generated but no "sub-collectionts".

    Any explaination for this?

    ScottGu's blog post says:  "The above association will cause cause the Product entity class to have a "Category" property that developers can use to access the Category entity for a given Product.  It will also cause the Category class to have a "Products" collection that enables developers to retrieve all products within that Category."

    Original link: http://weblogs.asp.net/scottgu/archive/2007/05/29/linq-to-sql-part-2-defining-our-data-model-classes.aspx


    Any help appreciated,
    LTG
    ecards
    Thursday, June 19, 2008 4:26 AM

Answers

  •  hdgreetings.com wrote:
    Hi Martin, thanks for your reply.

    Here's the magic - I can now use this for my text in the CategoryID column:

         Text='<%# Eval("Category.CategoryName") %>'

    So how does it know about "CategoryName" without me doing a select?


    Hi HDGreetings.com,
    The reason that you are able to to Eval("Category.CategoryName") is because the Link2SQL Object/Relational Mapper knows about the relationship between the two objects and automatically does the SELECT for you.  A SELECT does happen, but one of the nice things about Linq2SQL is that you specify the data you want and it will handle all the SQL for you.


    Sunday, June 29, 2008 1:28 PM

All replies

  • I'm not certain I understand what you're trying to achieve. Is the relationship between Car and CarStyle one-to-many? If so, you cannot retrieve the Name property of the CarStyle by calling it on a collection of CarStyles without using the Select method:

     

    db.Cars.Select(car => car.CarStyles).Select(carStyle => carStyle.Name);

    Thursday, June 19, 2008 6:38 PM
  • Hi Martin, thanks for your reply.

    The confusion is that YOU CAN retrieve the Name property of a foreign key WITHOUT using a Select method.

    My question is Why does this work below?

      1) Start with the Northwind database and drag/drop the tables onto the Linq designer
      2) Add a LinqDataSource to a new WebForm that selects only the Products table
      3)
    Add a GridView, connect it the the LinqDataSource, and make CategoryID a Template Field

    Here's the magic - I can now use this for my text in the CategoryID column:

         Text='<%# Eval("Category.CategoryName") %>'

    So how does it know about "CategoryName" without me doing a select?

    Best regards,
    LTG



    Friday, June 20, 2008 5:31 PM
  • I might be way off but it seems you are mixing several things.

     

    In the Northwind case, you have Products and Categories.

    Products has a Foreign Key to Categories.

     

    In the designer, dragging those tables will lead to

    - a Product Class

    - a Category Class

     

    - Product has a property of type Category

    - Category has a property of type EntitySet(of Product) (basically a collection of Product instances)

     

    Now, from a product P1, you can do

    P1.Category.Name to acces the name of the product's category.

     

    In the code from Scott Guthrie's blog you are actually considering a single Product object at a time, and accessing its category object, because the WebControl is iterating over each product through DataBinding.

     

    In your original post, you had

     db.Cars.CarStyles.Name

    This actually does not make sense. You are accessing the Cars table, which is basically a collection of the Car rows in your database. A collection has no property such as CarStyle. a Car instance though, does.

     

    The difference between your two snippets is that in the second case you are actually iterating over the collection (through DataBinding).

     

    So in your original example, you could use the same thing if you iterate your collection (VB style, sorry):

    Code Snippet

    For Each car in db.Cars

    Debug.Print(car.CarStyle)

    Next

     

     

    If you check your classes you will see the properties are here.

     

    After that it's a matter of what you are trying to do. If you want to access the collection of CarStyles that are actually referenced by cars, then you have several ways. The post above is one, another one is to select from the CarStyle table where the Cars.Count is not zero (again, in VB but I don't practice C# nearly enough to write queries in it off hand):

     

    Code Snippet

    Dim Q = From Style in db.CarStyles _

    Where Style.Cars.Count <> 0

    Select Style.Name

     

     

    Hope that helps.

    Saturday, June 28, 2008 8:25 PM
  •  hdgreetings.com wrote:
    Hi Martin, thanks for your reply.

    Here's the magic - I can now use this for my text in the CategoryID column:

         Text='<%# Eval("Category.CategoryName") %>'

    So how does it know about "CategoryName" without me doing a select?


    Hi HDGreetings.com,
    The reason that you are able to to Eval("Category.CategoryName") is because the Link2SQL Object/Relational Mapper knows about the relationship between the two objects and automatically does the SELECT for you.  A SELECT does happen, but one of the nice things about Linq2SQL is that you specify the data you want and it will handle all the SQL for you.


    Sunday, June 29, 2008 1:28 PM