Can someone help me to convert the below SQL query to Linq? I tried so many methods none of them are worked.
SELECT PM.Name, SUM(CO.TotalPrice)
FROM CustomerOrder CO, PaymentMethod PM
WHERE CO.PaymentID = PM.PaymentID
AND CO.Status = 'CL'
GROUP BY PM.Name
It's really straightforward. You just need a Navigation Property on CustomerOrder for PaymentMethod, and then you translate the group by and select, something like this:
select PM.Name,SUM(CO.TotalPrice)
from CustomerOrder CO, PaymentMethod PM
where CO.PaymentID=PM.PaymentID
and CO.Status='CL'
group by PM.Name
becomes
from o in db.CustomerOrder
where o.Status == 'CL'
group o by o.PaymentStatus.Name into g
select new { PaymentStatus = g.Key, Total = g.Sum(o => o.TotalPrice) };
