title: query and create objects with a one to many relationship using LINQ
body: <p>In the DB, I have a two tables with a one-to-many relationship:</p>
  
  <pre><code>orders          suborders
  -----------     -----------
  id              id
  name            order_id
                  name
  </code></pre>
  
  <p>I'd like to query these tables and end up with a list of order objects, each of which contains a list (or empty list) of suborder objects. I'd also like to do this in a single DB query so it performs well.</p>
  
  <p>In traditional SQL query land, I'd do something like (forgive the pseudocode):</p>
  
  <pre><code>rs = "select o.id, o.name, so.id, so.name from orders o left join suborders so on o.id = so.order_id order by o.id"
  
  orders = new List&lt;Order&gt;
  order = null
  foreach (row in rs) {
      if (order == null || row.get(o.id) != order.id) {
          order = new Order(row.get(o.id), row.get(o.name), new List&lt;Suborders&gt;)   
          orders.add(order)
      }    
  
      if (row.get(so.id) != null) {
          order.suborders.add(new Suborder(row.get(so.id) row.get(so.name))
      }
  }
  </code></pre>
  
  <p>Is there a way to get this same resulting object structure using LINQ-to-Entities? Note that I want to get new objects out of the query, not the Entity Framework generated objects.</p>
  
  <p>The following gets me close, but throws an exception: "LINQ to Entities does not recognize the method..."</p>
  
  <pre><code>var orders = 
      (from o in Context.orders
       join so in Context.suborders on o.id equals so.order_id into gj
       select new Order 
       {
           id = o.id,
           name = o.name,
           suborders = (from so in gj select new Suborder
           {
               so.id,
               so.name
           }).ToList()
       }).ToList();
  </code></pre>