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
Bad paging in simple.data query when it has a Eager-loading joins. It should paginates only the MainClass objects.
Example:
The SQL Query builded by Simple.Data is:
A suggested SQL Query is avilable in: https://groups.google.com/forum/?fromgroups=#!topic/simpledata/Kp9XhZQfdAc