Paging query error
Opened this issue · 0 comments
hyzx86 commented
Yessql 3.3.0 In OrchardCore
Database: SqlServer 2016
This code generates incorrect pagination syntax
var docs = YesSession.Query<ContentItem, ContentItemIndex>()
.Where(x => (x.Latest || x.Published)
&& x.ContentType == model.TypeName)
.OrderBy(x => x.Id);
var contentItems = await docs.Take(RebuildIndexPageSize).ListAsync();
while (contentItems.Any())
{
...
page++;
contentItems = await docs.Skip(page * RebuildIndexPageSize).Take(RebuildIndexPageSize).ListAsync();
}
An error occurred while reading the second page of data
SELECT
[Document].*
FROM
[Document]
INNER JOIN (
SELECT
[Document].[Id],
MAX ( [order_1] ) AS [order_1]
FROM
[Document]
INNER JOIN [ContentItemIndex] AS [ContentItemIndex_a1] ON [ContentItemIndex_a1].[DocumentId] = [Document].[Id]
WHERE
( ( ( [ContentItemIndex_a1].[Latest] = 1 ) OR ( [ContentItemIndex_a1].[Published] = 1 ) ) AND ( [ContentItemIndex_a1].[ContentType] = N'PoTrackingItem' ) )
GROUP BY
[Document].[Id][Document].[Id]
ORDER BY
[order_1] OFFSET 200 ROWS FETCH NEXT 100 ROWS ONLY
) AS [IndexQuery] ON [IndexQuery].[Id] = [Document].[Id]
ORDER BY
[order_1]