Regression: IndexOf (array_position) no longer works correctly (nullability issues)

[emwoutersen]

Bug description

When ordering entities based on the index in a given list, entities not present in the given list are listed last. However the .IndexOf() method in .NET returns -1 when the entity is not present in the given list, so those entities should be listed first. Both earlier versions of Npgsql (<9.0.0) and in memory evaluation correctly lists those entities first.

Minimal reproduction

The following code snippet can be used to reproduce the scenario:

using Microsoft.EntityFrameworkCore;

using var context = await CreateDatabaseAsync();

List<string> requiredIdOrder = ["David", "John"];
await context.AddRangeAsync(
        new Person { Name = "John" }, // Index = 1
        new Person { Name = "David" }, // Index = 0
        new Person { Name = "Michael" }  // Index = -1
    );

await context.SaveChangesAsync();


var orderInMemory = (await context.Set<Person>().ToListAsync())
    .OrderBy(x => requiredIdOrder.IndexOf(x.Name))
    .ToList();

var queryable = context.Set<Person>()
    .OrderBy(x => requiredIdOrder.IndexOf(x.Name));
var orderInDatabase = await queryable.ToListAsync();


var queryString = queryable.ToQueryString();

Console.WriteLine($"""
    Order when executed in memory: {string.Join(',', orderInMemory.Select(x => x.Name))}
    Order when executed in Database: {string.Join(',', orderInDatabase.Select(x => x.Name))}

    Executed SQL:
    {queryString}
    """);


async Task<TestDbContext> CreateDatabaseAsync()
{
    var options = new DbContextOptionsBuilder<TestDbContext>()
        .UseNpgsql("Host=localhost;Username=postgres;Password=example;Database=coalesce_test;")
        .Options;

    var context = new TestDbContext(options);

    await context.Database.EnsureDeletedAsync();
    await context.Database.EnsureCreatedAsync();

    return context;
}

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

class TestDbContext : DbContext
{
    public TestDbContext(DbContextOptions options) : base(options)
    {
            
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Person>()
            .HasKey(p => p.Name);
    }
}

Results

Based on the index of the names in the requiredIdOrder list, Michael should come first. However, when executed in the database, Michael comes last. The full results are as follows (v9.0.3):

Order when executed in memory: Michael,David,John
Order when executed in Database: David,John,Michael

Executed SQL:
-- @__requiredIdOrder_0={ 'David', 'John' } (DbType = Object)
SELECT p."Name"
FROM "Person" AS p
ORDER BY array_position(@__requiredIdOrder_0, p."Name") - 1

If exactly the same code is executed with Npgsql version 8.0.11, the correct results are returned:

Order when executed in memory: Michael,David,John
Order when executed in Database: Michael,David,John

Executed SQL:
-- @__requiredIdOrder_0={ 'David', 'John' } (DbType = Object)
SELECT p."Name"
FROM "Person" AS p
ORDER BY COALESCE(array_position(@__requiredIdOrder_0, p."Name") - 1, -1)

As can be seen in the provided SQLs, the CAOELSCE operator is missing in the later versions of the package.

[roji]

Confirmed; this regression was caused by dotnet/efcore#33814 on the EF side; see dotnet/efcore#33814 (comment). Once we understand what the best way forward is, I'll push a fix in a patch here.

Runnable minimal repro

await using var context = new BlogContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

await context.AddRangeAsync(
    new Person { Name = "John" }, // Index = 1
    new Person { Name = "David" }, // Index = 0
    new Person { Name = "Michael" });  // Index = -1
await context.SaveChangesAsync();

List<string> requiredIdOrder = ["David", "John"];

var orderInMemory = (await context.Set<Person>().ToListAsync())
    .OrderBy(x => requiredIdOrder.IndexOf(x.Name))
    .ToList();

var queryable = context.Set<Person>()
    .OrderBy(x => requiredIdOrder.IndexOf(x.Name));
var orderInDatabase = await queryable.ToListAsync();

Console.WriteLine($"""
                   Order when executed in memory: {string.Join(',', orderInMemory.Select(x => x.Name))}
                   Order when executed in Database: {string.Join(',', orderInDatabase.Select(x => x.Name))}
                   """);

public class BlogContext : DbContext
{
    public DbSet<Person> People { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql("Host=localhost;Username=test;Password=test")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

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