Take followed by Skip is generated incorrectly in Entity Framework #246

Closed
Emill opened this Issue May 29, 2014 · 0 comments

Projects

None yet

2 participants

@Emill
Member
Emill commented May 29, 2014

Consider the two queries

context.Table1.Take(5).OrderBy(x => x.ID).Skip(2)
context.Table1.OrderBy(x => x.ID).Skip(2).Take(5)

The first one should fetch at most 3 items from offset 2.
The second one should fetch at most 5 items from offset 2.

Npgsql treats both queries as the second one, i.e. the generated SQL is something like:

Select ... From Table1 Order By ID Asc Offset 2 Limit 5

In the first case, it should either subtract the offset value from the limit value (and make sure the new limit value is non-negative, otherwise use 0) when possible, or generate a subquery like

Select ... From (Select ... From Table1 Limit 5) As q Order by q.ID Asc Offset 2

Also consider the query

context.Table1.OrderByDescending(x => x.ID).Take(5).OrderBy(x => x.ID).Skip(2)

That one is also currently generating the same SQL as the two above. It completely misses the OrderByDescending expression.

A query that needs a subquery can be something like:

context.Table1.OrderByDescending(x => x.ID).Take(5).OrderBy(x => x.ID).Take(2)

But now it is only treated incorrectly as

context.Table1.OrderBy(x => x.ID).Take(2)

Or simply:

context.OrderByDescending(x => x.ID).Take(3).OrderBy(x => x.ID)

which is treated as

context.OrderBy(x => x.ID).Take(3)

Also consider nested Takes:

context.Table1.Take(3).Take(4)

That one currently fetches the first 4 items, while it only should fetch the first 3 items.

@Emill Emill closed this Jun 28, 2014
@franciscojunior franciscojunior added this to the 2.2 milestone Jul 1, 2014
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment