What problem are you trying to solve?

We have a table with filter conditions. Currently Ids are stored as arrays.

public class Conditions
{
    public string[] Property1Values { get; set; }
    public string[] Property2Values { get; set; }
}

Then we get values from other table based on those conditions:

var count = await dbContext.Conditions
    .Select(c => dbContext.Entities.Where(e => c.Property1Values.Contains(e.Property1) &&  c.Property2Values.Contains(e.Property2)).Count())
    .ToListAsync();

EF Core translates this query into something like this:

SELECT  (
    SELECT count(*)::int
    FROM "Entities" AS a0
    WHERE a."Property1Values" @> ARRAY[a0."Property1"]::text[] AND a."Property2Values" @> ARRAY[a0."Property2"]::text[])
FROM "Conditions" AS a

I.e. it creates an array from a single value, and compares them via @> operator. In this case Postgres ignores indexes, and on a huge table (60m rows) it executes more than 1 minute.

On the other hand, if I rewrite this query to this:

SELECT  (
    SELECT count(*)::int
    FROM "Entities" AS a0
    WHERE a0."Property1" = ANY(a."Property1Values") AND a0."Property2" = ANY(a."Property2Values"))
FROM "Conditions" AS a

It uses indexes for Property1 and Property2, and executes in ~1ms.

Describe the solution you'd like

EF Core should translate Array.Contains as ANY operator instead of creating single-element arrays.