Problem retrieving paged data
Posted: Mon 24 Jun 2013 16:56
I'm having a problem where I'm trying to retrieve data from a table in paged chunks using the Linq IQueryable helpers Skip and Take so something like
LinqConnect generates this SQL:
However the value passed to Take is not the upper index it's the number of items past x to take. If we use real numbers lets say we want page two with a page size of 10 (skip 10, take 10) we end up with an expression that can't true
so really it should be more like this:
using the previous example would be:
I'm currently on 4.2.229.0, has this been already fixed and we just need to upgrade or is this a new bug?
Code: Select all
context.Table.Skip(x).Take(y)Code: Select all
SELECT [t1].Column1, ...
FROM (
SELECT [t2].Column1, ...,
ROW_NUMBER() OVER (ORDER BY [t2].Column1, ...]) AS [rnum]
FROM (
SELECT [t3].Column1, ...
FROM Table [t3]
) [t2]
) [t1]
WHERE ([t1].[rnum] > @p0) AND ([t1].[rnum] <= @p1)
ORDER BY [t1].[rnum]
-- @p0: Input Int (Size = 0; DbType = Int32) [x]
-- @p1: Input Int (Size = 0; DbType = Int32) [y]"Code: Select all
([t1].[rnum] > 10) AND ([t1].[rnum] <= 10)Code: Select all
..snip...
WHERE ([t1].[rnum] > @p0) AND ([t1].[rnum] <= (@p0 + @p1))
..snip...Code: Select all
..snip...
WHERE ([t1].[rnum] > 10) AND ([t1].[rnum] <= 20)
..snip...