System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 8b7d179c3c535a15dc471a75ace400d959d1ec54
Title: EF: wrong native query generation -> Data Loss, query omits some results
Status: Closed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: LINQ Resolution: Fixed
Last Modified: 2011-07-09 09:03:32
Version Found In: 1.0.72.0
Description:
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.


mistachkin added on 2011-07-09 09:03:32 UTC:
Fixed by check-in [76e929f694].