Paging and Eager-loading joins (many to many)

[Oscar Alejandro Amado Sarmiento]

Hi

I try to make this query:

            IEnumerable<MainClass> mainClasssIntance = db.MainClass

                .FindAll(some condition   && another condition)

                .OrderBySomeProperty()

                .ThenById()

                .Skip(offset)

                .Take(limit)

                .With(db.MainClass.Jointable.ChildClass)

                .ToList<MainClass>();

I want to paginate the MainClass objects, but the query paginates all the query result, so it paginates the childclass and the mainclass objects.

Did I make a mistake or Simpla.Data has support for this? 

The SQL QUery 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=2000000000000000000

[Mark Rendle]

The SQL looks right. It will select the first eight rows from main table, and all child rows for each of those rows. Is that not what you're seeing?

--
You received this message because you are subscribed to the Google Groups "Simple.Data" group.
To unsubscribe from this group and stop receiving emails from it, send an email to simpledata+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Oscar Alejandro Amado Sarmiento]

Thank you Mark but that we want is that simple.data don't include the sql marked with red in the query base for the paging:

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

So that simple.data just use the MainClass table for the query base in the paging.

[Mark Rendle]

Oh bother, I see now. Could you raise an issue for this on GitHub please? I'll squash the bug soon as I can.

[Oscar Alejandro Amado Sarmiento]

Mark the issue is available in:  https://github.com/markrendle/Simple.Data/issues/295

Thanks for your help

[Mark Rendle]

Thank you. Will attend at earliest possiblity.

Mark Rendle

Founder & CEO

Oort Corporation

Makers of Zudio