Support array containment and overlap checks

[byrneciaran]

I have a simple Tasks table with a users text[] column. I'm looking to query if an existing set of users is contained in the users column.

string[] existingUsers = { "joe" };
var tasks = _db.Tasks.Where(x => existingUsers.Any(y => x.Users.Contains(y))).ToList();

which I thought translated to

WHERE x.Users <@ existingUsers

but is throwing the error

The LINQ expression 'DbSet<Task>
    .Where(h => __existingUsers_0
        .Any(y => h.Users
            .Contains(y)))' could not be translated

Are the <@ or @> operators supported?

I had been following here #431

EF Core 3.1.0-preview3.19554.8
Npgsql.EntityFrameworkCore.PostgreSQL 3.1.0-preview3

[roji]

The @> and <@ operators check whether an entire array is contained within another array, whereas your LINQ expression above seems to check if any of the existing users are contained in x.Users... You seem to be looking for the array overlap operator, i.e. WHERE x.Users && existingUsers - is that correct?

[byrneciaran]

To be honest I have use for both operators. I guess I'm finding it difficult to explore what array operators are supported right now. Ideally I can avoid client execution with my linq query.

Is && supported? The only implementation I can see is for range operators.

[roji]

I've submitted a PR to translate array containment and overlap, we should be able to merge this for the upcoming 3.1 release.

Note to self: update translation docs when merged (http://www.npgsql.org/efcore/mapping/array.html)