dotnet / dotnet/efcore

Union in collection subquery cannot be translated

Open
#34,849 6 comments 2 reactions 0 assignees View on GitHub
area-query customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

When I run the following query in EF Core 8:

```csharp
db.Users.Select(x => new AuthorizedUser
{
Id = x.Id,
DefaultLanguageCode = x.DefaultLanguageCode,
AllPermissions = x
.AssignedPermissions.Select(x => x.BusinessKey)
.Union(x.Roles.SelectMany(x => x.Permissions, (_, p) => p.BusinessKey))
.ToHashSet()
}).ToArray();
```
I get the error:

```
Unable to translate a collection subquery in a projection since either parent or the subquery doesn't project necessary information required to uniquely identify it and correctly generate results on the client side. This can happen when trying to correlate on keyless entity type. This can also happen for some cases of projection before 'Distinct' or some shapes of grouping key in case of 'GroupBy'. These should either contain all key properties of the entity that the operation is applied on, or only contain simple property access expressions.
```

`User.AssignedPermissions` and `User.Roles` are mapped as many-to-many relations. I tried several variants (including with `Join`, `Distinct`, explicit `Select` s etc), but all fail to translate. The Problem seems to be the `Union` operation (both sides of the union without the union seem to work fine).

Why is this?

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.