Optimize SQL generated by LINQ Query in Entity Framework 4.1 with one-to-many associations

Question

I am having problems with the Sql query generated by LINQ. Since my environment is quite large, I made a simple example that reflects my problem.

This is my model:

public class ClassA
{
    public int ID { get; set; }
    public virtual ICollection<ClassB> 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<ClassB> Children { get; set; }
}

Very simple, huh?

Well, this is my request:

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();

The problem is when this query translates to SQL:

(from x in db.ClassAs
 where x.ID == 2
 select x).First()

becomes:

SELECT TOP (1) 
[Extent1].[ID] AS [ID]
FROM [dbo].[ClassAs] AS [Extent1]
WHERE 2 = [Extent1].[ID]

and

from b in classA.Children
select b.Data).Skip(10).Take(10)

becomes:

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)

I want the generated query to look something like this:

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

The big problem is that class A → Class B always has a length of more than 10,000 lines, and so it is, all these lines are loaded into memory, and the paging is done in memory, but I want this paging to be done by SQL Server .

Any thoughts on how to do this?

Answer 1

linq-to-entity linq-to-objects. :

var classA = (from x in db.ClassAs
              where x.ID == 2
              select x).First();

- linq-to-entity. db.ClassAs, IQueryable , SQL First(). :

var classesB = (from b in classA.Children
                select b.Data).Skip(10).Take(10);

- linq-to-objects. (HashSet) . virtual, EF , . -.

, :

db.Entry(classA)
  .Collection(c => c.Children)
  .Query()
  .OrderBy(...) // You must order entities before you can use Skip and Take
  .Skip(10)
  .Take(10)
  .Load();

var classesB = classA.Children;