Paging and Eager-loading joins

[oamado]

Bad paging in simple.data query when it has a Eager-loading joins. It should paginates only the MainClass objects.

Example:

 IEnumerable<MainClass> mainClasssIntance = db.MainClass
                .FindAll(some condition   && another condition)
                .OrderBySomeProperty()
                .ThenById()
                .Skip(offset)
                .Take(limit)
                .With(db.MainClass.Jointable.ChildClass)
                .ToList<MainClass>();

The SQL Query builded by Simple.Data is:

exec sp_executesql N'
WITH __Data AS (SELECT [dbo].[MainClass].[ID],
     ROW_NUMBER() OVER(ORDER BY [dbo].[MainClass].[SomeProperty],
     [dbo].[MainClass].[ID]) AS [_#_]
from [dbo].[MainClass] LEFT JOIN [dbo].[JoinTable] ON ([dbo].[MainClass].[ID] = [dbo].[JoinTable].[MainClassID]) LEFT JOIN [dbo].[ChildClass] ON ([dbo].[ChildClass].[ID] = [dbo].[JoinTable].[ChildClassID]) WHERE ([dbo].[MainClass].[SomeProperty] > @p1 AND [dbo].[MainClass].[SomeProperty] <= @p2))



SELECT [dbo].[MainClass].[ID],
    [dbo].[MainClass].[SomeProperty],
    [dbo].[MainClass].[SomeProperty2],
    [dbo].[MainClass].[SomeProperty3],
    [dbo].[MainClass].[SomeProperty4],
    [dbo].[ChildClass].[ID] AS [__withn__ChildClass__ID],
    [dbo].[ChildClass].[SomeProperty] AS [__withn__ChildClass__SomeProperty],
    [dbo].[ChildClass].[SomeProperty2] AS [__withn__ChildClass__SomeProperty2] 
    FROM __Data 
        JOIN [dbo].[MainClass] 
            ON [dbo].[MainClass].[ID] = __Data.[ID] 
    LEFT JOIN [dbo].[JoinTable] 
        ON ([dbo].[MainClass].[ID] = [dbo].[JoinTable].[MainClassID]) 
    LEFT JOIN [dbo].[ChildClass] 
        ON ([dbo].[ChildClass].[ID] = [dbo].[JoinTable].[ChildClassID]) 
    WHERE ([dbo].[MainClass].[SomeProperty] > @p1 AND [dbo].[MainClass].[SomeProperty] <= @p2) AND [_#_] 
    BETWEEN 1 AND 8',
    N'@p1 bigint,
    @p2 bigint',
    @p1=0,
    @p2=200000

A suggested SQL Query is avilable in: https://groups.google.com/forum/?fromgroups=#!topic/simpledata/Kp9XhZQfdAc