title: Optimize SQL generated by LINQ Query in Entity Framework 4.1 with one-to-many associations
body: <p>I'm having some problems with the Sql query generated by LINQ, 
  Since my environment is quite big, I did a simple example that reflects my problem.</p>
  
  <p>This is my model:</p>
  
  <pre><code>public class ClassA
  {
      public int ID { get; set; }
      public virtual ICollection&lt;ClassB&gt; Children { get; set; }
  }
  
  public class ClassB
  {
      public int ID { get; set; }
      public string Data { get; set; }
  }
  
  public class ClassC
  {
      public int ID { get; set; }
      public virtual ICollection&lt;ClassB&gt; Children { get; set; }
  }
  </code></pre>
  
  <p>Very simple huh?</p>
  
  <p>Well, this is my query:</p>
  
  <pre><code>var classA = (from x in db.ClassAs
               where x.ID == 2
               select x).First();
  var classesB = (from b in classA.Children
                  select b.Data).Skip(10).Take(10);
  
  classesB.ToList();
  </code></pre>
  
  <p>The problem is when this query gets translated to SQL:</p>
  
  <pre><code>(from x in db.ClassAs
   where x.ID == 2
   select x).First()
  </code></pre>
  
  <p>becomes:</p>
  
  <pre><code>SELECT TOP (1) 
  [Extent1].[ID] AS [ID]
  FROM [dbo].[ClassAs] AS [Extent1]
  WHERE 2 = [Extent1].[ID]
  </code></pre>
  
  <p>and:</p>
  
  <pre><code>from b in classA.Children
  select b.Data).Skip(10).Take(10)
  </code></pre>
  
  <p>becomes:</p>
  
  <pre><code>SELECT 
  [Extent1].[ID] AS [ID], 
  [Extent1].[Data] AS [Data], 
  [Extent1].[ClassA_ID] AS [ClassA_ID]
  FROM [dbo].[ClassBs] AS [Extent1]
  WHERE ([Extent1].[ClassA_ID] IS NOT NULL) AND ([Extent1].[ClassA_ID] = @EntityKeyValue1)
  </code></pre>
  
  <p>I wish that generated query would be something like this:</p>
  
  <pre><code>SELECT [Data] AS [Data]
  FROM (SELECT 
          [Data] AS [Data],
          rownum = ROW_NUMBER() OVER (ORDER BY [B].[ID])
          FROM ClassBs AS B , ClassAs AS A 
          WHERE B.ClassA_ID = A.ID
          AND A.ID = 2) AS T1
  WHERE [t1].rownum BETWEEN 11 AND 20
  ORDER BY [t1].rownum
  </code></pre>
  
  <p>The big problem is that Class A -> Class B have always more than 10k Lines, and the way it is, all these lines are being loaded into memory, and the paging is being made in-memory, but I wish that this paging would be done by the SQL Server.</p>
  
  <p>Any thoughts on how to accomplish this?</p>