title: With Entity Framework how to create nested objects without one massive query resultset or hundreds of small queries?
body: <p>I'm using EF to populate objects which I then interact with in my business layer code. The objects have several levels but let's first simplify it to a typical master-detail example with <code>Order</code> and <code>OrderLine</code>.</p>
  <p>Let's say I need to retrieve 50 orders each of which has about 100 order lines, and I need all that data. What's the most efficient way of doing this in EF?</p>
  <p>If I do it like this:</p>
  <pre><code>var orders = context.Orders.Where(o =&gt; o.Status == &quot;Whatever&quot;)
                  .Include(order =&gt; order.OrderLines)
                  .ToList();
  </code></pre>
  <p>Then it'll run a single query that pulls back 50x100 rows, then it cleverly assembles that into 50 <code>Order</code> objects, each with their own <code>OrderLine</code> objects.</p>
  <p>If instead I don't do the <code>Include()</code> and I later iterate over each <code>Order</code> and its <code>OrderLines</code>, then it'll issue 50 separate queries to get the <code>OrderLine</code> data, i.e. one for each <code>Order</code>.</p>
  <p>So far the <code>.Include()</code> seems great: I pull back a bit more data than needed from SQL but that's much better than issuing 50 extra queries. But is there a way I can choose to issue 2 queries, one to get <code>Order</code> and one to get <code>OrderLine</code>, and have EF connect the objects automatically?</p>
  <p>A more realistic scenario where I want this is if the data is more complex. Let's say I want objects like this (where <code>Product</code> is the thing being bought in the <code>OrderLine</code> and <code>ProductPart</code> is a number of bits used to make the <code>Product</code>):</p>
  <pre><code>- Order
     - OrderLine (avg 100 per Order) 
         - Product (1 per OrderLine)
             - ProductPart (avg 20 per Product)
  </code></pre>
  <p>Now if I do a <code>.Include()</code> with <code>ProductPart</code>, it'll make the query results much bigger. Or if I don't <code>Include()</code> then it'll issue separate queries for each <code>Product</code>.</p>
  <p>Is there a third way where I can get all the <code>Order</code>, <code>OrderLine</code> and <code>Product</code> data in one query and all the <code>ProductPart</code> data in another query, and magically have EF connect the objects up for me?</p>
  <p><strong>UPDATE:</strong></p>
  <p>I just read about <a href="https://docs.microsoft.com/en-us/ef/core/querying/related-data" rel="nofollow noreferrer"><code>AsSplitQuery()</code></a> which seems to be what I'm looking for but is only available in EF Core 5 which isn't stable till Nov 2020 (?). I'm looking for a way to achieve this in EF6.</p>
  <p>A bit more research and I found <a href="https://entityframework.net/improve-ef-include-performance" rel="nofollow noreferrer">https://entityframework.net/improve-ef-include-performance</a> which suggests two approaches when you have multiple object types coming off the parent object:</p>
  <ol>
  <li><p>Execute multiple EF commands to pull back the same list of parent objects, but each time <code>Include()</code>-ing different child object types. EF will hook up the related objects it's already pulled from the db, apparently.</p>
  </li>
  <li><p>Use the EF+ library which seems it can do something like <code>AsSplitQuery()</code></p>
  </li>
  </ol>
  <p>I'm not sure if this works with my case where there's more levels of nesting rather than just different types of objects off the parent. My guess is yes, if I'm clever about it. Any tips?</p>