title: How to retrieve child data, via LINQ Include, in one query, to improve performance?
body: <p>I am using ASP.NET 4.5, MVC5, C#, LINQ, EF6, SQL Server 2012/SQL Azure.</p>
  
  <p>I need to significantly improve the efficiency of a complex query. In essence the purpose of the task is to copy a "sample" recordset with many child records. I am currently doing this via C# and LINQ. I suspect that I am requerying the database within multiple Foreach blocks, thus I am causing many calls to the database. While each query is small, the number of calls is not. It could be a 200+. I believe they call this is "N+1" problem.</p>
  
  <p>The following layout gives an idea of the relationships and hence queries.</p>
  
  <pre><code>Table1-&lt;Table1.1
        -&lt;Table1.2-&lt;Table1.2.1
                  -&lt;Table1.2.2-&lt;Table1.2.2.1
                              -&lt;Table1.2.2.2
  </code></pre>
  
  <p>Instead of using "Foreach" to bring back "Table1.1" etc, I want to bring back all related data in one hit to minimise the number of calls to the DB. I understand I need to use "Include". I have got as far as:</p>
  
  <pre><code>db.Table1.Where(r=&gt;r.Id=myId).Include(x=&gt;x.Table1.2).Include(x=&gt;x.Table1.2)
  </code></pre>
  
  <p>However I am not sure how to alter this statement to bring back data down to "Table1.2.2.2". This is my question.</p>
  
  <p>Thank you in advance.</p>
  
  <p>EDIT 1</p>
  
  <p>I found an initial answer. </p>
  
  <pre><code>db.Table1.Include(x=&gt;x.Table1.1) 
           .Include(x=&gt;x.Table1.2) 
           .Include(x=&gt;x.Table1.2.Select(y=&gt;y.Table1.2.1)
  </code></pre>
  
  <p>However I may not need the middle line, so the following may be fine.</p>
  
  <pre><code>db.Table1.Include(x=&gt;x.Table1.1) 
           .Include(x=&gt;x.Table1.2.Select(y=&gt;y.Table1.2.1)
  </code></pre>
  
  <p>Thoughts...</p>
  
  <p>EDIT2</p>
  
  <p>I also need to go to 5 levels down. I am finding that this one retrieve times out !! Whether this is because EF is confused on the compilation, or that the retrieve is too complicated or both I am unsure. There is possibly a limit on how many levels one can use "Include" ? Also I am unsure that by specifying the path to the grandchildren then the parent are automatically retrieved, or do you have to specify the parents separately?</p>