sebastienros/yessql

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]