Skip() and Take() Problem

[MAGomes]

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]

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?