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

Question

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.

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 query:

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 gets translated 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 wish that generated query would be 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 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.

Any thoughts on how to accomplish this?

Answer 1

You must differ between linq-to-entities and linq-to-objects. This:

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

is linq-to-entities. You are accessing db.ClassAs providing IQueryable to build expression tree which will be executed as SQL in the database when you call First(). But this:

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

is linq-to-objects. The query itself is defined on the collection (HashSet) and executed on that collection. Because your property is marked as virtual EF will trigger lazy loading for you and fill all data in that property to allow you executing that in memory query. It will never work in different way.

If you want to make database query for your related property you must use explicit loading instead of lazy loading:

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;