CODEWITHSUNDEEP
sqllinqgroup-by
Louisa
Louisa

Reputation: 550

SQL to LINQ, group by and sum

Trying write a simple SQL query as LINQ.

This is the SQL:

SELECT p.id, p.customer, SUM(pd.total_price)
FROM purchases p JOIN purchases_details pd ON p.id = pd.purchase_id
GROUP BY pd.purchase_id

My best LINQ so far:

var purchases_with_details = from p in db.Purchases
                             join pd in db.Purchases_details on p.Id equals pd.purchase_id
                             group pd by pd.purchase_id into pdg
                             select new { ??? };

Problem is that I can only write pdg.* in the select new { }.

I would like to write something like { p.id, p.customer, pdg.Sum(o => o.total_price) }

Anyone know how to do that?

Upvotes: 1

Views: 188

Answers (2)

Hemal
Hemal

Reputation: 3760

You can try this one

var purchases_with_details = from p in db.Purchases
                             join pd in db.Purchases_details on p.Id equals pd.purchase_id
                             group pd by pd.purchase_id into pdg
                             select new { 
                                 PurchaseID=pdg.Key.purchase_id,
                                 TOTAL=pdg.Sum(x=>x.total_price)
                             };

Upvotes: 0

Eldaniz Ismayilov
Eldaniz Ismayilov

Reputation: 856

Try:

     var purchases_with_details = from pd in db.Purchases_details
                                         group pd by pd.purchase_id into pdg
                                         //join *after* group
                                         join p in db.Purchases on pdg.Key equals p.Id
                                         select new
                                         {
                                             Id = p.Id,
                                             Cust =p.customer,
                                             TotalPrice = pdg.Sum(y => y.total_price)
                                         };

Upvotes: 1

Related Questions