Last()/LastOrDefault() on primitive collections is translated like First() — missing descending ordinality ordering

The issue

Projecting Last()/LastOrDefault() over a primitive collection property that is
mapped to a PostgreSQL array (e.g. List<int> → integer[]) generates SQL that is
identical to First() and returns the first element instead of the last one.

Reproducer

public class Foo
{
    public int Id { get; set; }
    public List<int> Values { get; set; } = new();
}

public class MyDbContext : DbContext
{
    public DbSet<Foo> Foos { get; set; } = null!;
    public MyDbContext(DbContextOptions<MyDbContext> options) : base(options) { }
}

// Seed a Foo with Values = [3], then append 5, so the array is [3, 5].

var last = db.Foos.Select(f => f.Values.Last()).First();                     // expected 5, returns 3
var first = db.Foos.Select(f => f.Values.First()).First();                   // returns 3 (correct)
var indexer = db.Foos.Select(f => f.Values[f.Values.Count - 1]).First();     // returns 5 (correct)

Generated SQL

Last():

SELECT (
    SELECT v.value
    FROM unnest(f."Values") AS v(value)
    LIMIT 1)
FROM "Foos" AS f

First() — identical:

SELECT (
    SELECT v.value
    FROM unnest(f."Values") AS v(value)
    LIMIT 1)
FROM "Foos" AS f

Expected

Last() should order by ordinality descending, e.g.:

SELECT (
    SELECT v.value
    FROM unnest(f."Values") WITH ORDINALITY AS v(value, ord)
    ORDER BY ord DESC
    LIMIT 1)
FROM "Foos" AS f

At the very least it must not be identical to First().

Comparison with other providers

SQLite / .NET 9 generates the correct SQL for Last() (note ORDER BY "v"."key" DESC):

SELECT (
    SELECT "v"."value"
    FROM json_each("f"."Values") AS "v"
    ORDER BY "v"."key" DESC
    LIMIT 1)
FROM "Foos" AS "f"

So EF Core's core "reverse + first" translation works, but the Npgsql provider does
not apply the descending ordinality ordering on the unnest subquery.

Workaround

Use the array indexer instead of Last():

f.Values[f.Values.Count - 1]

This translates to f."Values"[(cardinality(f."Values") - 1) + 1] and returns the
last element correctly.

Versions

Npgsql.EntityFrameworkCore.PostgreSQL: 10.0.3
Microsoft.EntityFrameworkCore: 10.0.11
PostgreSQL: 16
.NET: 10

AI Triage

The below is an AI-generated analysis and may contain inaccuracies.

Confirmed PostgreSQL-provider bug. With Values = [3, 5], both Last() and LastOrDefault() return 3 instead of 5. First() returns 3, and the array-indexer workaround returns 5. The issue type has been set to Bug.

Reproduction and version comparison

The standalone repro was built and executed using .NET 10. PostgreSQL runs used PostgreSQL 18.4, rather than the report's PostgreSQL 16.

Provider version	EF Core version	Result
Npgsql 8.0.4	8.0.6	Translation throws: `Queries performing 'Last' operation must have a deterministic sort order.`
Npgsql 8.0.8	8.0.8	`Last()` and `LastOrDefault()` incorrectly return `3`
Npgsql 9.0.0	9.0.0	Both incorrectly return `3`
Npgsql 10.0.2	10.0.11	Both incorrectly return `3`
Npgsql 10.0.3	10.0.11	Both incorrectly return `3`; matches the reported package versions
Npgsql 10.0.3	10.0.13	Both incorrectly return `3`; updating EF Core does not fix it
SQLite 10.0.11	10.0.11	Both correctly return `5`, with descending JSON-array-key ordering

This is not a new regression in 10.0.3. The wrong-result behavior is present at least as far back as 8.0.8. I did not establish a previously working release for the exact query: 8.0.4 rejects it instead of returning the correct result.

Cause and related work

NpgsqlUnnestPostprocessor ignores the first ordering when deciding whether ordinality is needed, then removes that ordering without checking its direction. That optimization is valid for ascending storage order, but removes the descending ordinality ordering needed by Last() and LastOrDefault(). The same logic remains in current main.

The minimized Last() query produces:

SELECT (
    SELECT v.value
    FROM unnest(f."Values") AS v(value)
    LIMIT 1)
FROM "Foos" AS f
LIMIT 1

The history connects this to #3209, which added explicit ordinality ordering and the leading-ordering cleanup while fixing #3207. That is related history, not a duplicate of this direct Last() case.

An existing open fix, #3925, targets this issue by preserving descending ordinality and adds regression coverage for both methods. I inspected its diff but did not execute the PR's tests.

I searched open and closed issues in dotnet/efcore and this repository; no matching EF Core duplicate was found. The SQLite comparison and Npgsql-specific cleanup point to keeping this issue here rather than moving it upstream.

minimal repro

Use a .NET 10 console project with Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3 and Microsoft.EntityFrameworkCore 10.0.11. Set Test__Npgsql__DefaultConnection to a disposable database: this program deletes and recreates it.

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

await using var db = new MyDbContext();
await db.Database.EnsureDeletedAsync();
await db.Database.EnsureCreatedAsync();
db.Foos.Add(new Foo { Values = [3, 5] });
await db.SaveChangesAsync();

var last = db.Foos.Select(f => f.Values.Last()).First();
if (last != 5)
{
    throw new InvalidOperationException($"Expected 5, but Last() returned {last}.");
}

public class MyDbContext : DbContext
{
    public DbSet<Foo> Foos => Set<Foo>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql(Environment.GetEnvironmentVariable("Test__Npgsql__DefaultConnection"))
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class Foo
{
    public int Id { get; set; }
    public List<int> Values { get; set; } = [];
}

The program compiles, executes the SQL above, and terminates with Expected 5, but Last() returned 3. Replacing the inner Last() with LastOrDefault() reproduces the same wrong value.

Navigation Menu

Repository navigation

Last()/LastOrDefault() on primitive collections is translated like First() — missing descending ordinality ordering #3909

The issue

Reproducer

Generated SQL

Expected

Comparison with other providers

Workaround

Versions

Related

AI Triage

Reproduction and version comparison

Cause and related work

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Last()/LastOrDefault() on primitive collections is translated like First() — missing descending ordinality ordering #3909

Description

The issue

Reproducer

Generated SQL

Expected

Comparison with other providers

Workaround

Versions

Related

Activity

sanjaysingh commented on Sep 30, 2026

roji commented on Oct 14, 2026

AI Triage

Reproduction and version comparison

Cause and related work

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions