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

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'.

CodePudding user response：

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);