title: EF Core nested Linq select results in N + 1 SQL queries
body: <p>I have a data model where a 'Top' object has between 0 and N 'Sub' objects. In SQL this is achieved with a foreign key <code>dbo.Sub.TopId</code>.</p>
  
  <pre><code>var query = context.Top
      //.Include(t =&gt; t.Sub) Doesn't seem to do anything
      .Select(t =&gt; new {
          prop1 = t.C1,
          prop2 = t.Sub.Select(s =&gt; new {
              prop21 = s.C3 //C3 is a column in the table 'Sub'
          })
          //.ToArray() results in N + 1 queries
      });
  var res = query.ToArray();
  </code></pre>
  
  <p>In Entity Framework 6 (with lazy-loading off) this Linq query would be converted to a <strong>single</strong> SQL query. The result would be fully loaded, so <code>res[0].prop2</code> would be an <code>IEnumerable&lt;SomeAnonymousType&gt;</code> which is already filled.</p>
  
  <p>When using EntityFrameworkCore (NuGet v1.1.0) however the sub-collection is not yet loaded and is of type:</p>
  
  <pre><code>System.Linq.Enumerable.WhereSelectEnumerableIterator&lt;Microsoft.EntityFrameworkCore.Storage.ValueBuffer, &lt;&gt;f__AnonymousType1&lt;string&gt;&gt;.
  </code></pre>
  
  <p>The data will not be loaded until you iterate over it, resulting in N + 1 queries. When i add <code>.ToArray()</code> to the query (as shown in comments) the data gets fully loaded into <code>var res</code>, using a SQL profiler however shows this isn't achieved in 1 SQL query anymore. For each 'Top' object a query on the 'Sub' table is executed.</p>
  
  <p>First specifying <code>.Include(t =&gt; t.Sub)</code> doesn't seem to change anything. The use of anonymous types doesn't seem to be the problem either, replacing the <code>new { ... }</code> blocks with <code>new MyPocoClass { ... }</code> doesn't change anything.</p>
  
  <p>My question is: <strong>Is there a way to get behavior similar to EF6, where all data is loaded immediately?</strong></p>
  
  <hr>
  
  <p><em>Note</em>: i realize that in this example the problem can be fixed by creating the anonymous objects in memory <em>after</em> executing the query like so:</p>
  
  <pre><code>var query2 = context.Top
      .Include(t =&gt; t.Sub)
      .ToArray()
      .Select(t =&gt; new //... select what is needed, fill anonymous types
  </code></pre>
  
  <p>However this is just an example, i do actually need the creation of objects to be part of the Linq query as AutoMapper uses this to fill DTOs in my project</p>
  
  <hr>
  
  <p><s>
  <strong>Update:</strong> Tested with the new EF Core 2.0, issue is stil present. (21-08-2017)</p>
  
  <p>Issue is tracked on <code>aspnet/EntityFrameworkCore</code> GitHub repo: <a href="https://github.com/aspnet/EntityFrameworkCore/issues/4007" rel="noreferrer">Issue 4007</a>
  </s></p>
  
  <p><s>
  <strong>Update:</strong> A year later, this issue has been fixed in version <code>2.1.0-preview1-final</code>. (2018-03-01) 
  </s></p>
  
  <p><strong>Update:</strong> EF version 2.1 has been released, it includes a fix. see my answer below. (2018-05-31)</p>