Databases
StackOverflow2013
Postsdbo
POHierarchical queries in LINQ
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHierarchical queries in LINQ
    text
    copied!<p>Here is a simple LINQ query (based on NorthWind) that returns a list of Customers. Each customer contains a list of Orders.</p> <pre><code>from c in Customers join o in Orders on c.CustomerID equals o.CustomerID into CO select new {c, CO} </code></pre> <p>This works fine and the SQL generated is also fine. Now I want to go one step further. I want each Order object to contain a list of OrderDetails. I am using the following query:</p> <pre><code>from c in Customers join od in ( from o in Orders join od in OrderDetails on o.OrderID equals od.OrderID into OD select new { o.CustomerID, o, OD } ) on c.CustomerID equals od.CustomerID into COD select new { c, COD } </code></pre> <p>This query works but generates horrible SQL. A separate query is issued for each Customer. When you look at the lambda code we have:</p> <pre><code>Customers .GroupJoin ( Orders .GroupJoin ( OrderDetails, o =&gt; o.OrderID, od =&gt; od.OrderID, (o, OD) =&gt; new { CustomerID = o.CustomerID, o = o, OD = OD } ), c =&gt; c.CustomerID, od =&gt; od.CustomerID, (c, COD) =&gt; new { c = c, COD = COD } ) </code></pre> <p>The nested GroupJoins seem to be the cause of the multiple SQL stataments. However, I have tried various combinations without success. Any ideas?</p> <p>EDIT: I may have been a little unclear about what I was trying to achieve. I want the OrderDetail object to be a property of the Order object, which is in turn a property of the Customer object. I do not want Order &amp; OrderDetail to be properties of Customer. I am trying to get list of unique customers. For each customer I expect to a list of Orders and for each Order I want a list of OrderDetails. I want the hierarchy to go one level deeper than my original query.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload