none
How to write correct LinQ Statement RRS feed

  • Question

  • Hey
    I don't know, how I should write the correct LinQ.
    What I want is, that I get hierachial data, like it is generated from the Database.
    There I have a tree like this:
    Customer
    |--Orders
        |--Order Details

    Company
      |--Order Details

    So, The Customer->Orders->Order Details, I think is a self explaining thing.
    In the Order Details, there I have the ID from the companys, which holds the artice of the Order Details

    How is the correct LinQ now created, so I do get all relations of this?
    I tried the following, but it doesn't work:

                Dim myKunde = From Kunde In dc.Kundenstamms _
                              From Auftrag In dc.Auftrags _
                              Join Aufträge In dc.Auftrags On Kunde.Kundenid Equals Aufträge.Kundenid _
                              Join Details In dc.Auftragdetails On Aufträge.Id Equals Details.Auftragid _
                              Join Firmen In dc.Lieferantens On Details.Lieferant Equals Firmen.Id _
                              Where Kunde.Firmenid = VisualOPTNet.GetFirmenID AndAlso Aufträge.Glasliste=1 _
                              Select New With {.Kunden = Kunde, _
                                               .Aufträge = Aufträge, _
                                               .Details = Details, _
                                               .Firmen = Firmen}
    So, can someone please tell me, how this would work right? -
    THX
    Thursday, October 8, 2009 3:02 PM

Answers

  • You seem to have included 'Auftrags' twice, once without any join criteria. Try removing the second line in the query:

    from Auftrag In dc.Auftrags _

    ...as it doesn't appear to be used anywhere else in the query...?

    Also, what do you mean by hierarchical data? Retrieving it in a single query as per the above will flatten it. Do you want to maintain the same hierarchy within the results as the FKs/associations between the tables/classes?

    If so, you could either:
    a) Query just the customer table and then use the navigation properties together with L2S's lazy loading of associations/nav properties. That however can be very 'database chatty' depending on what you app does with the data, since the lazy loading thing will issue a new query for every nav property as you access or enumerate them.

    b) Turn it into three queries and merge it together client side. This can populate the entities so they can be used the same way as (a), but with fewer db roundtrips.
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Marked as answer by Yichun_Feng Wednesday, October 14, 2009 6:33 AM
    Friday, October 9, 2009 1:19 AM
    Answerer
  • Oh, I almost forgot to mention one thing. You can get the first two levels (Customers + Orders) with DataLoadOptions, although that will result in a lot of duplicated data flying over the wire when querying the DB since the query issued by L2S flattens the data when using DataLoadOptions and then packs it up into hierarchical data when materializing the objects. And DataLoadOptions won't take the third level, so won't cover your scenario completely...

    As for an example of (b) - check out the sample in my reply to this thread:
    http://social.msdn.microsoft.com/Forums/en/linqtosql/thread/6a823875-2a7e-4952-872d-de30aa462965

    That sample does this but as two queries for retrieving two levels of data that it then merges in the app. You can easily use the same technique and add a third query for the order details level. The key is just that you start off with a base query, and for each level project out just the entity you're interested in for that level, in order to avoid duplicating any data across that has to travel across the wire...

    .
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Marked as answer by Yichun_Feng Wednesday, October 14, 2009 6:33 AM
    Friday, October 9, 2009 9:21 AM
    Answerer

All replies

  • You seem to have included 'Auftrags' twice, once without any join criteria. Try removing the second line in the query:

    from Auftrag In dc.Auftrags _

    ...as it doesn't appear to be used anywhere else in the query...?

    Also, what do you mean by hierarchical data? Retrieving it in a single query as per the above will flatten it. Do you want to maintain the same hierarchy within the results as the FKs/associations between the tables/classes?

    If so, you could either:
    a) Query just the customer table and then use the navigation properties together with L2S's lazy loading of associations/nav properties. That however can be very 'database chatty' depending on what you app does with the data, since the lazy loading thing will issue a new query for every nav property as you access or enumerate them.

    b) Turn it into three queries and merge it together client side. This can populate the entities so they can be used the same way as (a), but with fewer db roundtrips.
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Marked as answer by Yichun_Feng Wednesday, October 14, 2009 6:33 AM
    Friday, October 9, 2009 1:19 AM
    Answerer
  • Hey

    Thanks for your answer!
    Plan (b) sounds good for me. - Can you give me a little sample, that I can see how you mean it?

    THX
    Friday, October 9, 2009 7:55 AM
  • Oh, I almost forgot to mention one thing. You can get the first two levels (Customers + Orders) with DataLoadOptions, although that will result in a lot of duplicated data flying over the wire when querying the DB since the query issued by L2S flattens the data when using DataLoadOptions and then packs it up into hierarchical data when materializing the objects. And DataLoadOptions won't take the third level, so won't cover your scenario completely...

    As for an example of (b) - check out the sample in my reply to this thread:
    http://social.msdn.microsoft.com/Forums/en/linqtosql/thread/6a823875-2a7e-4952-872d-de30aa462965

    That sample does this but as two queries for retrieving two levels of data that it then merges in the app. You can easily use the same technique and add a third query for the order details level. The key is just that you start off with a base query, and for each level project out just the entity you're interested in for that level, in order to avoid duplicating any data across that has to travel across the wire...

    .
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Marked as answer by Yichun_Feng Wednesday, October 14, 2009 6:33 AM
    Friday, October 9, 2009 9:21 AM
    Answerer