Asp.net Core REST API The LINQ expression Could not be Translated

Sulfy 0
Viewed 70

Asp.net REST API , 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'

    var query = from orders in DbContext.JobOrders where orders.OrderStat == 1
                            join users in DbContext.users on orders.Uid equals users.Uid into ousers
                            from onusers in ousers.DefaultIfEmpty()
                            join customers in DbContext.customers on orders.Uid equals customers.Uid into offlinecustomers
                            from offusers in offlinecustomers.DefaultIfEmpty()
                            join orderitems in DbContext.OrderItems on orders.Eid equals orderitems.OrderId into orderitems
                            from orderitem in orderitems.DefaultIfEmpty().GroupBy(i => i.OrderId).Select(g => new { qty = g.Sum(s => s.Qty == null ? 0 : s.Qty) })
                            select new
                            {
                                Eid = orders.Eid,
                                Uid = orders.Uid,
                                OrderStat = orders.OrderStat,
                                Name = onusers.Name == null ? offusers.Name : onusers.Name,
                                Contact = onusers.Contact == null ? offusers.Contact : onusers.Contact,
                                Qty = orderitem.qty
                            };

The code works fine if I remove orederitems table querying including group by clause.

The exact same code was working in asp.net and stopped working when I moved to asp.net Core.

Please Help

linq asp.net asp.net-core c# api
1 Answers

Try following :

var query = (from orders in DbContext.JobOrders where orders.OrderStat == 1
                            join users in DbContext.users on orders.Uid equals users.Uid into ousers
                            from onusers in ousers.DefaultIfEmpty()
                            join customers in DbContext.customers on orders.Uid equals customers.Uid into offlinecustomers
                            from offusers in offlinecustomers.DefaultIfEmpty()
                            join orderitems in DbContext.OrderItems on orders.Eid equals orderitems.OrderId into orderitems
                            from orderitem in orderitems.DefaultIfEmpty()
                            select new {orderitem = orderitem, users = users, onusers = onusers, offusers = offusers}
                            ).GroupBy(i => i.orderitem.OrderId)
                            .Select(g => new { 
                                orderId = g.Key,
                                qty = g.orderitem.Sum(s => s.qty == null ? 0 : s.qty),
                                Eid = g.orders.FirstOrDefault().Eid,
                                Uid = g.orders.FirstOrDefault().Uid,
                                OrderStat = g.orders.FirstOrDefault().OrderStat,
                                Name = g.onusers.FirstOrDefault().Name == null ? g.offusers.FirstOrDefault().Name : g.onusers.FirstOrDefault().Name,
                                Contact = g.onusers.FirstOrDefault().Contact == null ? g.offusers.FirstOrDefault().Contact : g.onusers.FirstOrDefaut().Contact
                            });
jdweng0
Related