Skip and take ignored when query has join

[richardgergely]

I have 2 tables with a one to many relation.

Code: Select all

public class Car
{
    public int Id { get; set; }
    public string LicencePlateNumber { get; set; }

    public int PersonId { get; set; }
    public Person Person { get; set; }
}

public class Person
{
    public Id { get; set; }
    public string Name{ get; set; }

    public ICollection<Car> Cars { get; set; } = new HashSet<Car>();
}

When I run this query, I get all of the cars, and the skip and take is ignored.

Code: Select all

List<Car> cars = await DbContext.Cars.Include(c => c.Person).Skip(1).Take(1).ToListAsync();

When I remove the include, so there is no join in the query, it returns only the expected one car.

I've tried EF Core 3 and 3.1, both produces the same result, but on EF Core 2.2 it works correctly.

Can you please check it?

[Shalex]

When I run this query, I get all of the cars, and the skip and take is ignored.

Code: Select all

List<Car> cars = await DbContext.Cars.Include(c => c.Person).Skip(1).Take(1).ToListAsync();

1. Please enable the dbMonitor tool and specify the generated SQL statement.
2. You are working with v9.10.909, aren't you?
3. Tell us the version (xx.x.x.x) of your Oracle Server.

[richardgergely]

1.
This was the SQL without include:

Code: Select all

SELECT "t".ID, "t".LICENCEPLATENUMBER, "c".PERSONID
FROM (
    SELECT "c".ID, "c".LICENCEPLATENUMBER, "c".PERSONID, ROWNUM "ROWNUM"
    FROM CARS "c"
) "t"
WHERE ("t"."ROWNUM" > :p__p_0) AND ("t"."ROWNUM" <= (:p__p_0 + :p__p_0))

p__p_0 = 1

And this is what I get with include:

Code: Select all

SELECT "t".ID, "t".LICENCEPLATENUMBER, "t".PERSONID, "p".ID, "p".NAME
FROM (
    SELECT "c".ID, "c".LICENCEPLATENUMBER, "c".PERSONID
    FROM CARS "c"
) "t"
INNER JOIN PERSON "p" ON "t".PERSONID = "p".ID

2. Yes
3. 11.2.0.4.0

[Shalex]

We have reproduced the bug with Oracle 11g and below.

[Shalex]

The bug with paging in EF Core 3, when using JOINs and subqueries to Oracle 11.2 and below, is fixed in v9.11.951: viewtopic.php?f=1&t=39884.