Solved

Sql-to-linq Group By and Sum Problem

Posted on 2010-08-13
1
489 Views
Last Modified: 2013-11-11
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.

Thanks in advance

Diana
0
Comment
Question by:dianar77
1 Comment
 

Accepted Solution

by:
dianar77 earned 0 total points
ID: 33433520
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question