Issue

This Content is from Stack Overflow. Question asked by Sulfy

I have two MS SQL Server tables for job orders “JobOrders” and for Order Items “OrderItems”, I would like to retrieve data of each “JobOrders” and order Quantity from “OrderItems” with My ASP.Net Core Rest API Controller.

My Two Tables and Desired Result as follows:-

My Controller code as follows:-

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

Solution

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

This Question was asked in StackOverflow by Sulfy and Answered by Svyatoslav Danyliv It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.