Hello,
When calling the Skip() method with a starting index other than 0, the method automatically appends additional columns 'in addition to' the existing order-by column. These additional order-by columns consist of the remaining columns that are not in the sort expression. Great job for LINQ to handle this automatically for us as it provides deterministic sorting on the data returned;however, when 0 is passed to the Skip() method (when querying for the first page for instance), it appears that the method is optimized and the additional order-by columns aforementioned is not rendered. This is problematic as the sort condition is inconsistent between Skip(0) and Skip(n).
Another developer, Dave, also pointed out the same issue. Quote: "Skip(0) should generate the same row numbering sql that skip(n) does..." A link to the thread: http://www.eggheadcafe.com/conversation.aspx?messageid=32045245&threadid=32045239
Does anyone encounter a similar issue? Fortunately the data we're querying has an identity column, so a work around we came up is to always append the identity column to the existing sort expression. We would love to hear other creative approaches or solutions.
Example
Both queries below select the same columns from the database and include an descent ordering on the column, RequestReceivedDate. Notice how the query generated by Skip(10) append additional ordering for the remaining columns which causes a different data ordering when there are records with the same RequestReceivedDate. The idea of appending these extra sorting is great; however, the library should implement the same logic for Skip(0) so both Skip(0) and Skip(n) would return data in the same order.
[Query generated by Skip(0).Take(10)]
SELECT TOP (10) [t31].[PersonId], [t31].[value] AS [RequestReceivedDate2], ....
FROM (
SELECT [t0].[PersonId], (
SELECT MAX([t8].[RequestReceivedDate])
FROM [dbo].[EnrollRequest] AS [t8]
WHERE EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[DomainAccount] AS [t9]
WHERE ([t9].[DomainAccountId] = [t8].[DomainAccountId]) AND ([t9].[PersonId] = ([t0].[PersonId]))
)
) AS [value], ...
) AS [t31]
ORDER BY [t31].[value] DESC
[Query generated by Skip(10).Take(10)]
SELECT [t32].[PersonId], [t32].[value] AS [RequestReceivedDate2], ...
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t31].[value] DESC) AS [ROW_NUMBER], [t31].[PersonId], [t31].[value], [t31].[FullAccountName], [t31].[LastName], ...
FROM (
SELECT [t0].[PersonId], (
SELECT MAX([t8].[RequestReceivedDate])
FROM [dbo].[EnrollRequest] AS [t8]
WHERE EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[DomainAccount] AS [t9]
WHERE ([t9].[DomainAccountId] = [t8].[DomainAccountId]) AND ([t9].[PersonId] = ([t0].[PersonId]))
)
) AS [value], ...
) AS [t32]
WHERE [t32].[ROW_NUMBER] BETWEEN @p21 + 1 AND @p21 + @p22
ORDER BY [t32].[ROW_NUMBER]