IQueryable Concat throws "Unable to translate set operation after client projection has been applied" EF Core 8.0.2

[hankrum]

Type of issue

Code doesn't work

Description

var q1 = _diagnosisDataRepository
    .Entities
    .Where(ddi => !ddi.DataDefinition.Instances.Any() && ddi.DataDefinition.DataStorageId == id)
    .Where(did => did.DiagnosisDefinition.Instances.Any())
    .Select((DiagnosisDefinitionIdsDataDefinitionId ddi) => new DataDefinitionCustom
    {
        DataDefinitionId = ddi.DataDefinition.Id,
        Instance = ddi.DiagnosisDefinition.Instances.First(),
    });

var q2 = _dataDefinitionRepository
    .Entities
    .Where(dd => dd.DataStorageId == id && dd.Instances != null && dd.Instances.Any()).AsQueryable()
    .Select((DataDefinition dd) => new DataDefinitionCustom
    {
        DataDefinitionId = dd.Id,
        Instance = dd.Instances.First() 
    });

var q3 = q1.Concat(q2);

var t1 = q1.ToQueryString();
var t2 = q2.ToQueryString();

var q4 = q3.ToArray();

[Enter feedback here]
This throws an exception

Unable to translate set operation after client projection has been applied. Consider moving the set operation before the last ‘Select’ call…

On the other hand when I get the t1 and t2 sql for the requests and execute then on the database with UNION ALL, everything is fine. So it is some Entity Framework bug. Does anyone have an idea how to avoid it?

Page URL

https://learn.microsoft.com/en-us/dotnet/api/system.linq.queryable.concat?view=net-8.0

Content source URL

https://github.com/dotnet/dotnet-api-docs/blob/main/xml/System.Linq/Queryable.xml

Document Version Independent Id

e5092ed5-37ad-fd2e-451f-ef49a5225856

Article author

@dotnet-bot

I couldn't figure out the best area label to add to this issue. If you have write-permissions please help me learn by adding exactly one area label.

I couldn't figure out the best area label to add to this issue. If you have write-permissions please help me learn by adding exactly one area label.

[michaelgsharp]

@ajcvickers

[roji]

@hankrum sorry it took so long to respond - this looks like a duplicate of #28350. As the error says, the problem is with having the Select() before applying the Concat; EF is unfortnuately limited on applying set operations over projections in this manner. Manually using SQL as you indicated you're doing is a good workaround for now.