ASP.NET Core Web API controller; the LINQ expression could not be translated

Question

I have two SQL Server tables JobOrders and OrderItems. I would like to retrieve data of each JobOrders and order quantity from OrderItems in my ASP.NET Core Web API controller.

My two tables and desired result as follows:

JobOrders

Eid	orderID	OrderStatus
1	1001	1
2	1002	5
3	1003	3

OrderItems

Eid	orderID	OrderStatus	Qty
1	1001	abc	2
2	1001	adc	1
3	1002	jcb	5
4	1002	vbb	2
5	1002	xyz	8
6	1003	akp	4

Intended Result

[
{
OrderID : 1001,
OrderStatus : 1,
Qty : 3
},
{
OrderID : 1002,
OrderStatus : 5,
Qty : 15
},
{
OrderID : 1003,
OrderStatus : 3,
Qty : 4
}
]

This is my controller code:

var query = from orders in myDbContext.JobOrders 
            join allorderitems in myDbContext.OrderItems on orders.Eid equals allorderitems.OrderId into orderitems
            from orderitem in orderitems
                 .GroupBy(i => i.OrderId)
                 .Select(g => new
                              {
                                  Qty = g.Sum(i => i.Qty),
                              })
            select new
                   {
                       OrderID = orders.Oid,
                       OrderStatus = orders.Status,
                       Qty = orderitem.Qty,
                   };

var result = query.OrderByDescending(m => m.OrderID).ToList();

return Ok(result);

Postman returns:

500 Internal Server error:

System.InvalidOperationException: The LINQ expression 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'.

Answer 1

Do not use GroupJoin if you do not plan to do LETF JOIN. It has limited support in EF Core.

var query = 
    from order in myDbContext.JobOrders 
    join oi in myDbContext.OrderItems on order.Eid equals oi.OrderId
    group oi by new { order.OrderId, order.Status } into g
    select new
    {
        OrderID = g.Key.OrderId,
        OrderStatus = g.Key.Status,
        Qty = g.Sum(x => x.Qty)
    };

var result = query.OrderByDescending(m => m.OrderID).ToList();

return Ok(result);