title: Hierarchical queries in LINQ
body: <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>