InsufficientInformationToIdentifyElementOfCollectionJoin error with join of join selection in queries with union

[Howaner]

In ef core 6, the selection of joins of joins in a unioned query is not working anymore. The code worked in ef core 5.

Exception

System.InvalidOperationException: 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.
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplyProjection(Expression shaperExpression, ResultCardinality resultCardinality, QuerySplittingBehavior querySplittingBehavior)
   at Microsoft.EntityFrameworkCore.Query.Internal.SelectExpressionProjectionApplyingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPostprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at SerpaPlus.Backend.DataAccess.ModulNews.NewsDBDataAccess.GetReadableNews(Int64 p_MandantID, NewsFilterTypeEnum p_FilterType) in C:\Development\SerpaPlus\3-Develop\SerpaPlus.Backend\DataAccess\ModulNews\NewsDBDataAccess.cs:line 493
   at SerpaPlus.Web.Api.Controllers.NewsController.GetReadableNews(Int64 p_MandantID, NewsFilterTypeEnum p_FilterType) in C:\Development\SerpaPlus\3-Develop\SerpaPlus.Web.Api\SerpaPlus.Web.Api\Controllers\NewsController.cs:line 248
   at lambda_method871(Closure , Object )
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at SerpaPlus.Web.Api.Middlewares.RequestLoggingMiddleware.Invoke(HttpContext p_Context) in C:\Development\SerpaPlus\3-Develop\SerpaPlus.Web.Api\SerpaPlus.Web.Api\Middlewares\RequestLoggingMiddleware.cs:line 64
   at SerpaPlus.Web.Api.Middlewares.UserSessionMiddleware.InvokeAsync(HttpContext p_Context) in C:\Development\SerpaPlus\3-Develop\SerpaPlus.Web.Api\SerpaPlus.Web.Api\Middlewares\UserSessionMiddleware.cs:line 57
   at Microsoft.AspNetCore.Authorization.Policy.AuthorizationMiddlewareResultHandler.HandleAsync(RequestDelegate next, HttpContext context, AuthorizationPolicy policy, PolicyAuthorizationResult authorizeResult)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware.<Invoke>g__Awaited|6_0(ExceptionHandlerMiddleware middleware, HttpContext context, Task task)

Code examples

// Not working
var test = await dbContext.NewsUsers.Select(x => x.News)
    .Union(dbContext.News.AsQueryable().Where(x => x.Priority == NewsPriorityEnum.SystemNews))
    .Select(x => new
    {
        NewsID = x.ID.Value,
        Kategorien = x.KategorienEF.Select(k => k.Kategorie)  // this is the problem
    })
    .ToListAsync();

// Working
var test = await dbContext.NewsUsers.Select(x => x.News)
    .Union(dbContext.News.AsQueryable().Where(x => x.Priority == NewsPriorityEnum.SystemNews))
    .Select(x => new
    {
        NewsID = x.ID.Value,
        Kategorien = x.KategorienEF
    })
    .ToListAsync();

// Working
var test = await dbContext.NewsUsers.Select(x => x.News)
    .Select(x => new
    {
        NewsID = x.ID.Value,
        Kategorien = x.KategorienEF.Select(k => k.Kategorie)
    })
    .ToListAsync();

// Working
var test = await dbContext.NewsUsers.Select(x => x.News)
    .Union(dbContext.News.AsQueryable().Where(x => x.Priority == NewsPriorityEnum.SystemNews))
    .Select(x => new
    {
        NewsID = x.ID.Value,
        // Kategorien = x.KategorienEF.Select(k => k.Kategorie)
    })
    .ToListAsync();

// Working
var test = await dbContext.News.AsQueryable().Where(x => x.ID == 5)
    .Union(dbContext.News.AsQueryable().Where(x => x.Priority == NewsPriorityEnum.SystemNews))
    .Select(x => new
    {
        NewsID = x.ID.Value,
        Kategorien = x.KategorienEF.Select(k => k.Kategorie)
    })
    .ToListAsync();

Unfortunatly I need to directly address the NewsUsers view to get performant results from the database. If I directly select the news table and join into the NewsUsers view, the database optimizer is not working correctly and the query takes ages to execute.

Provider and version information

EF Core version: 6.0.12 Database provider: FirebirdSql.Data.FirebirdClient v9.1.0 Target framework: .NET 6.0 Operating system: Win10 21H2 IDE: Visual Studio 2022 17.4.2

[ajcvickers]

Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

[Howaner]

I attached a simple test project EFBug01.zip

[maumar]

related/caused by: https://github.com/dotnet/efcore/issues/15873

[ajcvickers]

Note from triage: while the query did not throw an error in EF Core 5, it may have returned incorrect results depending on the ordering from the database.