InvalidOperationException "sequence contains no matching element" in TableReferenceExpression.get_Table

[neoGeneva]

File a bug

Since migrating to EFCore 6 I'm getting an InvalidOperationException with the following code, I'm not 100% sure what the issue is, something to do with the GroupBy with the Max, and then a join I believe.

Include your code

using Microsoft.EntityFrameworkCore;

var options = new DbContextOptionsBuilder<BugDbContext>()
   .UseSqlServer()
   .Options;

using var db = new BugDbContext(options);

var orderId = 123456;

var orderItems = db.order_item
    .Where(x => x.order_id == orderId);

var items = orderItems
    .GroupBy(x => x.order_id, (x, g) => new
    {
        Key = x,
        is_pending = g.Max(y => y.actual_shipping_date == null && y.date_item_cancelled == null ? x : (x - 10000000))
    })
    .OrderBy(x => x.Key);

var itemResults = orderItems
    .Join(items, x => x.order_id, x => x.Key, (x, y) => x)
    .OrderBy(x => x.order_id);

Console.WriteLine(itemResults.ToQueryString());

public class BugDbContext : DbContext
{
    public BugDbContext(DbContextOptions<BugDbContext> options)
        : base(options)
    { }

    public DbSet<order_item> order_item { get; set; }
}

public class order_item
{
    public int id { get; set; }
    public int order_id { get; set; }
    public DateTime? actual_shipping_date { get; set; }
    public DateTime? date_item_cancelled { get; set; }
}

Include stack traces

System.InvalidOperationException: Sequence contains no matching element
   at System.Linq.ThrowHelper.ThrowNoMatchException()
   at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source, Func`2 predicate)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.TableReferenceExpression.get_Table()
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ConcreteColumnExpression.get_Table()
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.<>c__DisplayClass115_0.<ContainsTableReference>b__0(TableExpressionBase e)
   at System.Linq.Enumerable.Any[TSource](IEnumerable`1 source, Func`2 predicate)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ContainsTableReference(ColumnExpression column)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ColumnExpressionReplacingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.CaseExpression.VisitChildren(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.VisitExtension(Expression node)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ColumnExpressionReplacingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlFunctionExpression.VisitChildren(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.VisitExtension(Expression node)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ColumnExpressionReplacingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.TryLiftGroupByAggregate(SqlExpression sqlExpression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.AddToProjection(SqlExpression sqlExpression, String alias, Boolean assignUniqueTableAlias)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.PushdownIntoSubqueryInternal()
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.AddJoin(JoinType joinType, SelectExpression& innerSelectExpression, SqlExpression joinPredicate)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.AddJoin(JoinType joinType, SelectExpression innerSelectExpression, Expression outerShaper, Expression innerShaper, SqlExpression joinPredicate)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.AddInnerJoin(ShapedQueryExpression innerSource, SqlExpression joinPredicate, Expression outerShaper)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateJoin(ShapedQueryExpression outer, ShapedQueryExpression inner, LambdaExpression outerKeySelector, LambdaExpression innerKeySelector, LambdaExpression resultSelector)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   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__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToQueryString(IQueryable source)
   at Program.<Main>$(String[] args) in C:\Projects\EFBug\Program.cs:line 27

Include provider and version information

EF Core version:
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: NET 6.0
Operating system: Windows 11
IDE: VSCode 1.62.0

[neoGeneva]

Hey, I've got a bit closer to understanding the cause, it's something to do with the g.Max() referencing they key x from the join before it.

[smitpatel]

Root cause:
Any query which uses component of grouping key inside aggregate (through chaining or inside the lambda of aggregate operation), which is eventually pushed down will cause the error.

[neoGeneva]

Woo! Thanks @smitpatel and @ajcvickers, you folks are awesome!

[ajcvickers]

FYI for those impacted by this issue: EF Core 6.0.1 is now available from NuGet.