GROUP and SUM in Entity Framework

entity-framework, group-by, linq-to-entities, select, sum

Solution

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),
            }

Problem

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)

Original source