`arrayColumn.Contains(element)` translates into `element = ANY(arrayColumn)` in version 10

[sskalex]

Hello! I believe I've encountered a regression while upgrading Npgsql.EntityFrameworkCore.PostgreSQL from 9.0.4 to 10.0.2.

arrayColumn.Contains(element) used to translate into [arrayColumn @> ARRAY[element]] as stated in docs.
But now it translates into element = ANY(arrayColumn). This can be critical since element = ANY(arrayColumn) operator is not supported by GIN index. Now my query seq scans the table.

Minimal repro code

using Microsoft.EntityFrameworkCore;
using Npgsql;

var dataSource = new NpgsqlDataSourceBuilder("Host=localhost")
    .Build();

var options = new DbContextOptionsBuilder<TestContext>()
    .UseNpgsql(dataSource)
    .EnableSensitiveDataLogging()
    .Options;

await using var ctx = new TestContext(options);

var query = ctx.Items.Where(i => i.Names.Contains("Test"));

Console.WriteLine(query.ToQueryString());

public class TestContext : DbContext
{
    public TestContext(DbContextOptions<TestContext> options) : base(options)
    {
    }

    public DbSet<Item> Items => Set<Item>();
}

public class Item
{
    public int Id { get; set; }

    public string[] Names { get; set; } = Array.Empty<string>();

    public int[] Numbers { get; set; } = Array.Empty<int>();
}

This repro code produces:

SELECT i."Id", i."Names", i."Numbers"
FROM "Items" AS i
WHERE 'Test' = ANY (i."Names")

I've tested various expressions and I can't make it produce @> as it used to before. All variations produce WHERE 'Test' = ANY (i."Names"). To name a few I've tried:

• var query = ctx.Items.Where(i => i.Names.AsEnumerable().Contains("Test"));
• var query = ctx.Items.Where(i => i.Names.ToList().Contains("Test"));
• var query = ctx.Items.Where(i => i.Names.Any(n => n == "test"));

With Npgsql.EntityFrameworkCore.PostgreSQL 9.0.4 all those variations translate to:

SELECT i."Id", i."Names", i."Numbers"
FROM "Items" AS i
WHERE i."Names" @> ARRAY['test']::text[]

This query is supported by GIN index and I believe it is the correct translation.

P.S. - this feels related to a C# 14 breaking change of overload resolution with span parameters. That was my original thought but adding AsEnumerable() or downgrading to C# 10 didn't help. Maybe this change somehow affected the library itself but I am probably very wrong here.

Environment

Component	Version
.NET	10.0
C#	14 (also fails on 10)
Microsoft.EntityFrameworkCore	10.0.9
Npgsql.EntityFrameworkCore.PostgreSQL	10.0.2

[sskalex]

I found a workaround that translates into && PG operator that does use GIN index:

var names = new List<string?> { "Test" };

var query = ctx.Items.Where(i => i.Names.Any(n => names.Contains(n)));

This query translates into:

-- @names={ 'Test' } (DbType = Object)
SELECT i."Id", i."Names", i."Numbers"
FROM "Items" AS i
WHERE i."Names" && @names

It's not pretty and pretty fragile though.

[roji]

You're probably running into #3546 - in 10 we started varying the SQL we generate based on whether EF is aware of an index or not. The reason is that the x @> ARRAY[y]::text[] translation is great when there's a GIN index over x, but on the other hand if there isn't, it doesn't use indexes over y, which y = ANY (x) does. So my guess is that you're missing the actual index definition in your EF model; maybe you're not using migrations at all so you were assuming you don't need it. Try adding that HasIndex() and you should see the SQL revert to what it was pre-10.0.

[sskalex]

Thank you for the response! Indeed we don't have index definitions in EF models. Adding HasIndex with method "gin" works:

        modelBuilder.Entity<Item>()
            .HasIndex(i => i.Names)
            .HasMethod("gin");

With that query does translate to what it was pre-10.0 👍

The reason is that the x @> ARRAY[y]::text[] translation is great when there's a GIN index over x, but on the other hand if there isn't, it doesn't use indexes over y, which y = ANY (x) does.

Thats makes sense, but only if y is a column. In our case y is just a parameter. I think varying the generated SQL based on whether EF is aware of an index is cool. However imho it is still a pretty dangerous change. Maybe the behaviour when no indexes are specified should've remained the same? Please consider that.

We were pretty lucky that query with arrays is not on our hot-path. I believe that updating can lead to critical issues for others.

[roji]

Sorry for the late reply.

Well, we have two query forms here: one is good for when the arrays is an indexed column, and the other is good for when the element is an indexed column. Disregarding indexes, I think that overall, between the two query forms, element = ANY(array) is much more expected/idiomatic construct than array @> ARRAY[element]::text[], which is why that's the new default translation, unless we're told that the array is an indexed column.

But you're right about the behavior change here, and the fact that it be quite an unpleasant surprise to people upgrading to 10. I still think the change is good (and doing another change at this point will only risk breaking people yet again, in the other direction), but I'm adding a breaking change note to help people be more aware of this.

[sskalex]

I agree with the decision. Thank you!

[roji]

Here's the release note: npgsql/doc#464.

BTW what probably makes this less critical (you're the first/only person to react to the change) is that people usually use EF to create their database, so they already have the HasIndex in place; this only affects people whose model doesn't fully correspond to the database etc.

One more thing - I suspect that when there isn't an index, x = ANY (y) may perform better, simply because it's a simpler, more primitive construct which PostgreSQL might be able to "see through" better... Just to give another reason why I think it's the better default.

But anyway - all good.