Broken "Include" after "Skip"

[Hizeqwane]

Bug description

Hello.
I found a problem with "Skip" when querying an entity with a negative ID - the "Include" mechanism breaks.
Initially, the query was more complex, but I managed to simplify it to the state shown in the screenshots.

Reproduced on packages:
Microsoft.EntityFrameworkCore.Design Version 8.0.11
Microsoft.EntityFrameworkCore.Tools Version 8.0.11
Npgsql Version 8.0.6
Npgsql.EntityFrameworkCore.PostgreSQL Version 8.0.11

Unfortunately, there is no way to check on later versions.

P.S. I'm sorry, but I don't have the ability to write a console application that reproduces the issue.

Your code

var normalObj = await context
            .Set<MyObject>()
            .AsNoTracking()
            .Include(s => s.Kinds)
            .FirstAsync(s => s.Id == -8001);
        
        var brokenIncludeList = await context
            .Set<MyObject>()
            .AsNoTracking()
            .Include(s => s.Kinds)
            .Skip(1)
            .ToListAsync();

        var brokenInclude = brokenIncludeList.First(s => s.Id == -8001);

Stack traces

The error is related to the system's behavior rather than the thrown exception

Verbose output

EF Core version

8.0.11

Database provider

Npgsql

Target framework

.NET 8.0

Operating system

Windows 10

IDE

JetBrains Rider 2023.3.4

[AndriySvyryd]

Unfortunately, we can't investigate this until you are able to provide a small runnable repro project.

[Hizeqwane]

Please take a look, I'm attaching a console application that reproduces the situation.
TestBug.zip

SQL to fill data

CREATE TABLE "Kind" (
    "Id" INTEGER PRIMARY KEY
);

CREATE TABLE "MyObject" (
    "Id" INTEGER PRIMARY KEY
);

ALTER TABLE "Kind" 
ADD COLUMN "MyObjectId" INTEGER REFERENCES "MyObject"("Id");

INSERT INTO "Kind" ("Id") 
VALUES (1), (2);

INSERT INTO "MyObject" ("Id") 
VALUES (-80000), (-80001);

UPDATE "Kind" 
SET "MyObjectId" = -80000 
WHERE "Id" = 1;

UPDATE "Kind" 
SET "MyObjectId" = -80001 
WHERE "Id" = 2;

Additional information:

• If you use any other methods (Take, Where, etc.) after Skip, Include is restored again.

[roji]

Your query uses Skip() without specifying any ordering:

var brokenIncludeList = await context
    .Set<MyObject>()
    .AsNoTracking()
    .Include(s => s.Kinds)
    .Skip(1)
    .ToListAsync();

Since the ordering is undefined, that means that any arbitrary row can get skipped. Now, since you also specify split queries, the two queries can (but don't have to) return different results (since the ordering is unspecified), returning bad results.

This has been fixed for EF 10 (see #26808).