I want to select sum of all (paid) prices of an order item for each customer. Here is SQL command:
SELECT c.name,SUM(oi.price * oi.count) from customer c
JOIN order o ON c.id=o.customer_id
JOIN order_item oi ON o.id=oi.order_id
JOIN bill b ON b.id=oi.bill_id
WHERE b.payment_id is NOT null
GROUP by c.name;
I don't know how to do this in EF. Example result:
John Smith 1500,2
Allan Babel 202,0
Tina Crown 3500,78
(comma is used as decimal point..because price is decimal value)
Your example result doesn't seem to match your SQL command, but i think you are looking for something like this:
var query = from c in context.Customers
join o in context.Orders on c.id equals o.customer_id
join oi in context.OrderItems on o.id equals oi.order_id
join b in context.bill on oi.bill_id equals b.id
where b.payment_id != null
group oi by c.name into g
select new
{
Name = g.Key,
Sum = g.Sum(oi => oi.price * oi.count),
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With