• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

Sql-to-linq Group By and Sum Problem

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
dianar77
Asked:
dianar77
1 Solution
 
dianar77Author Commented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now