Solved

Sql-to-linq Group By and Sum Problem

Posted on 2010-08-13
486 Views
How can I make a linq for this query

SELECT     SUM(CASE Positive
WHEN 0 THEN HowMuch
WHEN 1 THEN HowMuch * (- 1)
END)
AS Positive
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
select new
{
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.

Diana
0
Question by:dianar77
1 Comment

Accepted Solution

dianar77 earned 0 total points
var total1 = 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
select new
{
ID = 1,
value = (r.Positive)?p.HowMuch:p.HowMuch*(-1)
};

var total2 = from p in total1
group p by p.ID into g
select new
{
g.Key,
total = g.Sum(p => p.value)
};
0

Featured Post

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…