SelectMany on list/array types should translate to unnest

[FiniteReality]

I have a C# query which looks something like this:

var validGuildIds = new[] { 1, 2, 3, 4 };
await _authDbContext.Groups
    .Where(group => group.GuildIds.Any(x => validGuildIds.Contains(x)))
    .SelectMany(x => x.GuildIds)
    .ToArrayAsync();

The goal is to find any rows with a GuildIds column which contains an element from validGuildIds, since I later want to invert that query and show the user only elements from validGuildIds which weren't found in the database.

Following the docs, I'd expect this query to expand to something like this, using parametrised queries as appropriate:

SELECT unnest("GuildIds") FROM "Groups" WHERE "GuildIds" && ARRAY[1, 2, 3, 4];

However, I instead get an exception that the expression x => x.GuildIds could not be translated.

My project is configured to use Npgsql.EntityFrameworkCore.PostgreSQL version 5.0.0-rc1, since that's the latest on NuGet.

[roji]

Translating top-level SelectMany is a complex task that would require changes in the EF Core as well.

However, your array intersection operation does translate, so you can work around this by executing the SelectMany on the client as below. The WHERE clause gets executed on the server, which is the important part - it doesn't really matter if the "merging" projection happens on the server or on the client.

var results = ctx.Groups
    .Where(group => group.GuildIds.Any(x => validGuildIds.Contains(x)))
    .AsEnumerable()
    .SelectMany(x => x.GuildIds)
    .ToArray();

(use System.Interactive.Async for async client-side LINQ operators)

Leaving open to track implementing this in the future at some point.

[evilz]

What about a EF.Functions.unnest special function ?

[roji]

@evilz that wouldn't necessary reduce the complexity of the translation.

[roji]

I'll put this on 8.0 as a possible improvement (but there's a good chance I won't get around to it...)