Bug in Devart.Data.Oracle.EFCore 9.5.527 with paging - not returning all the rows

[df5]

My sample Entity <OprDate> has a total of 4,523 rows. My Odata Controller has a page size of 1000.

• The first query generated by Devart EF issued returns the correct rows (note p_p_0).

http://localhost:59109/odata/oprdate?$f ... count=true

Executed DbCommand (366ms) [Parameters=[p__p_0='1001' (Nullable = true)], CommandType='Text', CommandTimeout='0']
SELECT "t".OPR_DATE
FROM (
SELECT "$it".OPR_DATE
FROM VU_NCPA_FINALSCHEDULES "$it"
WHERE ("$it".OPR_DATE = TO_DATE('2018-05-01', 'yyyy-mm-dd')) AND ("$it".OPR_DATE = TO_DATE('2018-05-01', 'yyyy-mm-dd'))
ORDER BY "$it".OPR_DATE
) "t"
WHERE ROWNUM <= :p__p_0

• And the first call returns the odata next link: "@odata.nextLink": "http://localhost:59109/odata/oprdate?$f ... $skip=1000" which skips 1000 due to the page size.
• The next link of skip=1000 above generates a SQL of:

Executed DbCommand (33ms) [Parameters=[p__TypedProperty_0='1000' (Nullable = true), p__TypedProperty_1='1000' (Nullable = true), p__p_2='1001' (Nullable = true)], CommandType='Text', CommandTimeout='0']
SELECT "t1".OPR_DATE
FROM (
SELECT "t0".OPR_DATE, ROW_NUMBER() OVER (ORDER BY "t0".OPR_DATE) AS "ROWNUM"
FROM (
SELECT "t".OPR_DATE
FROM (
SELECT "$it".OPR_DATE, ROW_NUMBER() OVER (ORDER BY "$it".OPR_DATE) AS "ROWNUM"
FROM VU_NCPA_FINALSCHEDULES "$it"
WHERE "$it".OPR_DATE = TO_DATE('2018-05-01', 'yyyy-mm-dd')
ORDER BY "$it".OPR_DATE
) "t"
WHERE "t"."ROWNUM" > :p__TypedProperty_0
) "t0"
WHERE "t0".OPR_DATE = TO_DATE('2018-05-01', 'yyyy-mm-dd')
ORDER BY "t0".OPR_DATE
) "t1"
WHERE ("t1"."ROWNUM" > :p__TypedProperty_1) AND ("t1"."ROWNUM" <= (:p__TypedProperty_1 + :p__p_2))

• This generated query parameters is incorrect and causes rows to be skipped. I only get a total of 2,523 rows instead of 4,523 when I follow all the odata nextlink. There are 2 solutions that I can think of:
1. The :p__TypedProperty_1 should be set to zero (0) and :p__p_2 set to 1000 since this is my page size.

2. The DevArt library could change how it generates the SQL to :
SELECT "t1".OPR_DATE
FROM (
SELECT "t0".OPR_DATE, ROW_NUMBER() OVER (ORDER BY "t0".OPR_DATE) AS "ROWNUM"
FROM (
SELECT "t".OPR_DATE
FROM (
SELECT "$it".OPR_DATE, ROW_NUMBER() OVER (ORDER BY "$it".OPR_DATE) AS "ROWNUM"
FROM VU_NCPA_FINALSCHEDULES "$it"
WHERE "$it".OPR_DATE = TO_DATE('2018-05-01', 'yyyy-mm-dd')
ORDER BY "$it".OPR_DATE
) "t"
WHERE "t"."ROWNUM" > :p__TypedProperty_0
) "t0"
WHERE "t0".OPR_DATE = TO_DATE('2018-05-01', 'yyyy-mm-dd')
ORDER BY "t0".OPR_DATE
) "t1"
WHERE "t1"."ROWNUM" <= ( :p__p_2)

In this case :p__TypedProperty_0 will be set to the $skip value and :p__p_2 will be set to the page size value of 1000.

[df5]

Sorry, please ignore this post this is not a bug, found out it has to do with OData Controllers.

[df5]

Sorry, please ignore this post this is not a bug, found out it has to do with OData Controllers.

[Shalex]

Thank you for letting us know.