EF Core 3 Linq could not be translated

Question

I tried to build a query in ef core 3.0 that gets the full process from the db server

 IEnumerable<int> stIds = stateIds;
  var rtables = await db.Order.
                Join(db.OrderDetail, order => order.OrderId, orderdetail => orderdetail.OrderId, (order, orderdetail) => new { order, orderdetail }).
                Where(x => x.order.SellerId == sellerId && stIds.Contains(x.orderdetail.OrderStateId)&&x.order.RtableId != null)
                .GroupBy(x =>             
                x.order.RtableId               
                )
                .Select(x => new RtableState { RtableId = x.Key ?? 0, OrderStateId = x.OrderByDescending(x => x.orderdetail.OrderStateId).Select(x => x.orderdetail.OrderStateId).FirstOrDefault() }).ToListAsync();

I get this error:

{ "Message": "Processing of the LINQ expression 'AsQueryable<<>f__AnonymousType52>(OrderByDescending<<>f__AnonymousType52, int>(\r\n source: NavigationTreeExpression\r\n Value: default(IGrouping, <>f__AnonymousType52>)\r\n Expression: (Unhandled parameter: e), \r\n keySelector: (x) => x.orderdetail.OrderStateId))' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.", "Inner": "" }

I know the query is too complex for EF Core 3.0, but is this a bug or should it not work?

My solution is to split the request.

IEnumerable<int> stIds = stateIds;
            var rtableStatesServer = await db.Order.
                Join(db.OrderDetail, order => order.OrderId, orderdetail => orderdetail.OrderId, (order, orderdetail) => new { order, orderdetail }).
                Where(x => x.order.SellerId == sellerId && stIds.Contains(x.orderdetail.OrderStateId) && x.order.RtableId != null)
                .GroupBy(x => new RtableState
                {
                    RtableId =

                x.order.RtableId ?? 0,
                    OrderStateId = x.orderdetail.OrderStateId
                })
                .Select(x => new RtableState { RtableId = x.Key.RtableId, OrderStateId = x.Key.OrderStateId }).ToListAsync();


            var rtableStates = rtableStatesServer.GroupBy(r => r.RtableId,
                        (key, value) => new RtableState
                        {
                            RtableId = key,
                            OrderStateId = value.OrderByDescending(x=>x.OrderStateId).Select(x => x.OrderStateId).FirstOrDefault()
                        }).ToList();

Answer 1

As indicated in the exception message, the problem is caused by the expression

x.OrderByDescending(y => y.orderdetail.OrderStateId)
    .Select(y => y.orderdetail.OrderStateId)
    .FirstOrDefault()

where x is IGrouping<,> produced by GroupBy operator.

This may indicate either a bug or a limitation in EF Core.

I would consider it a limitation, which might never be fixed because GroupBy result containing expressions other than key and aggregate expressions have no natural SQL equivalent.

The general solution is to avoid GroupBy where possible and use alternative constructs with correlated subqueries. But this particular query has simple natural solution because the expression

set.OrderByDescending(item => item.Property).Select(item => itm.Property).FirstOfDefault() 

can be expressed with

set.Max(item => item.Property)

which is a standard (thus supported aggregate).

Replace the aforementioned problematic expression with

x.Max(y => y.orderdetail.OrderStateId)

and the problem will be solved.