Skip() and Take() Problem

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
MAGomes
Posts: 1
Joined: Mon 18 Jun 2012 14:22

Skip() and Take() Problem

Post by MAGomes » Mon 18 Jun 2012 14:31

Hello,

When using Skip() and Take() to paginate a view, the result is wrong. After seeing the generated query I could see the problem is because it is using RowNum instead "View"."row_number", for example:

wrong:
WHERE "Project1"."row_number" > 0 AND ROWNUM <= 1

right:
WHERE "Project1"."row_number" > 0 AND "Project1"."row_number" <= 1

How can I solve it?
Thanks,
Murilo.

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Re: Skip() and Take() Problem

Post by Shalex » Sat 23 Jun 2012 12:34

Code: Select all

using (ALEXSHEntities context = new ALEXSHEntities()) {
    var a = context.DEPTVIEWs.OrderBy("it.DEPTNO").Skip(10).Take(10).ToList();
}
generates the following SQL:

Code: Select all

SELECT 
"Extent1".DEPTNO,
"Extent1".DNAME,
"Extent1".LOC
FROM (
	SELECT 
	"Extent1".DEPTNO,
	"Extent1".DNAME,
	"Extent1".LOC, row_number() OVER (ORDER BY "Extent1".DEPTNO ASC) AS "row_number"
	FROM ALEXSH.DEPT_VIEW "Extent1"
	ORDER BY "Extent1".DEPTNO ASC
) "Extent1"
WHERE "Extent1"."row_number" > 10 AND ROWNUM <= 10
ORDER BY "Extent1".DEPTNO ASC
As a result, the records (11-20) are retrieved from the database. Could you please tell us why you consider this behaviour as incorrect?

Post Reply