InvalidOperationException with GroupJoin() without SelectMany() on a temp table

[Phen-Ro]

Are GroupJoin()s on temp tables supported? Specifically when the result selector has a non-scalar property. (Not sure how to describe this exactly.) I receive a InvalidOperationException when trying to use them, and the exception is different depending on if it's one or two. However, if I follow the GroupJoin() with a a SelectMany(), it works fine.

Here's an anonymized example with one GroupJoin:

await using var tempTable = await _context.BulkInsertValuesIntoTempTableAsync(ids); // ids is an IList<int>

var example = tempTable.Query
    .GroupJoin(_context.TableOne, id => id, one => one.OneId, (id, ones) => new { id, ones })
    .ToList();

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 Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPostprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerQueryTranslationPostprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutorExpression[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass11_0`1.<ExecuteCore>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteCore[TResult](Expression query, Boolean async, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Testing.Inventory.Api.Controllers.DatabaseTestController.TestTempTable(IList`1 ids)
   at ...

and with two:

await using var tempTable = await _context.BulkInsertValuesIntoTempTableAsync(ids); // ids is an IList<int>

var example = tempTable.Query
    .GroupJoin(_context.TableOne, id => id, one => one.OneId, (id, ones) => new { id, ones })
    .GroupJoin(_context.TableTwo, a => a.id, two => two.TwoId, (a, twos) => new { a.id, a.ones, twos })
    .ToList();

System.InvalidOperationException: The LINQ expression 'DbSet<TempTable<int>>("Thinktecture:TempTable:Thinktecture.EntityFrameworkCore.TempTables.TempTable<int>")
    .FromTempTable(
    )
    .Select(t => t.Column1)
    .GroupJoin(
        inner: DbSet<TableOne>(), 
        outerKeySelector: id => id, 
        innerKeySelector: one => one.OneId, 
        resultSelector: (id, ones) => new { 
            id = id, 
            ones = ones
         })' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutorExpression[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass11_0`1.<ExecuteCore>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteCore[TResult](Expression query, Boolean async, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Testing.Inventory.Api.Controllers.DatabaseTestController.TestTempTable(IList`1 ids)
   at ...

Yet this form to flatten the interior projection works fine:

var thisWorks = tempTable.Query
    .GroupJoin(_context.TableOne, id => id, one => one.OneId, (id, ones) => new { id, ones })
    .SelectMany(a => a.ones.DefaultIfEmpty(), (a, one) => new { a.id, one })
    .ToList();

[PawelGerr]

Did you try replacing tempTable with a regular table?
I just tested it with a regular table and run into System.InvalidOperationException as well. In general, GroupJoin has very limited support when used with EF Core.

[Phen-Ro]

For me, those queries work with a regular table. For example,

var example = _context.TableOne
    .GroupJoin(_context.TableOne, id => id.OneId, one => one.OneId, (id, ones) => new { id, ones })
    .ToList();

or

var example = _context.TableOne
    .Select(one => one.OneId)
    .GroupJoin(_context.TableOne, id => id, one => one.OneId, (id, ones) => new { id, ones })
    .ToList();

[PawelGerr]

But not with a query that has two GroupJoin, right?

var example = _context.RealTable
    .GroupJoin(_context.TableOne, id => id, one => one.OneId, (id, ones) => new { id, ones })
    .GroupJoin(_context.TableTwo, a => a.id, two => two.TwoId, (a, twos) => new { a.id, a.ones, twos })
    .ToList();

Until EF supports queries like this, there's not much I can do.

[Phen-Ro]

That is true - EF complains about translating more than one GroupJoin() even for plain queries.

What got me started on this path was your LeftJoin() extension, which is much easier to use than straight LINQ, but using it multiple times threw exceptions. I tried to pare it down for a minimal example, but that just introduced a red herring.

Now that I am seeing the GroupJoin() limitations for what they are, the real problem is that my queries need to flatten out their properties in order to use GroupJoin() properly, including when I use LeftJoin(). That is, having multiple collections inside the result selector trips up EF.

There still is an odd discrepancy between using a temp table an a regular table when using just one GroupJoin() where the result includes a collection, but that's pretty easy to avoid doing.

I'll go ahead and close this issue. Thank you for your time!