Sql-to-linq Group By and Sum Problem
Posted on 2010-08-13
How can I make a linq for this query
SELECT SUM(CASE Positive
WHEN 0 THEN HowMuch
WHEN 1 THEN HowMuch * (- 1)
FROM dbo.Table1 INNER JOIN
dbo.Table2 ON dbo.Table1.Table1ID = dbo.Table2.Table1ID INNER JOIN
dbo.Table3 ON dbo.Table2.Table2ID = dbo.Table3.Table2ID
var query = from p in db.Table1
join q in db.Table2 on p.table1ID equals q.table1ID
join r in db.Table3 on q.table2ID equals r.table2ID
group r by ?????? into g
total = g.Sum(p => p.value)
furthermore how can I make the +/- in the sum
right now I can do it in two ways
- by steps, but that is not an elegant way.
I am getting the query group by table3, and at the end I add all of them.
sum = query3.Sum(p=>p.HowMuch); but this one can have the +/-
- Also I can make the query directly.
but I don't like it. I would like to get a nice idea.
Thanks in advance