var documents =
entities.Document
.OrderBy(p => p.Weight)
.Skip(pageSize*pageNumber)
.Take(pageSize);
This query will omit all records in page requested if record has the same Weight as records from previous pages.
Example records. Page size 1. Page number 6 (zero-based). Columns: Id and Weight.
1 1
2 2
3 3
4 4
5 5
6 6
7 6
Last page (#6, zero-based) will return no elements.
Detail: I use index over Weight column (Collate empty, ASC, AutoIncrement false). I don't think it has any effect.
Explanation can be found in ToTraceString() returned by query.
Query tries to find all records that should be skipped.
Then it skips all records that have values (in fields used for OrderBy) the same as in those records that should be skip. It will work if Weight field always contains unique values. But will fail if values are not unique.
Problem line (from listing below):
WHERE ([Var_68].[Weight] = [Var_70].[Weight]) OR (([Var_68].[Weight] IS
Full ToTraceString() result listing:
SELECT
[Var_68].[Id] AS [Id],
[Var_68].[Weight] AS [Weight]
FROM [Document] AS [Var_68]
WHERE NOT (EXISTS (SELECT [Var_70].[Weight] AS [Weight]
FROM ( SELECT
[Extent1].[Weight] AS [Weight]
FROM [Document] AS [Extent1]
ORDER BY [Extent1].[Weight] ASC LIMIT 6
) AS [Var_70]
WHERE ([Var_68].[Weight] = [Var_70].[Weight]) OR (([Var_68].[Weight] IS NULL) AND ([Var_70].[Weight] IS NULL))))
ORDER BY [Var_68].[Weight] ASC LIMIT 1
It may be better to transform this EF query to something just simple:
SELECT
[Var_68].[Id] AS [Id],
[Var_68].[Weight] AS [Weight]
FROM [Document] AS [Var_68]
ORDER BY [Var_68].[Weight] ASC LIMIT 1 OFFSET 6
Ivan Akcheurov.
<hr /><i>mistachkin added on 2011-07-09 09:03:32 UTC:</i><br />
Fixed by check-in [76e929f694].
|
