Solved

Sql-to-linq Group By and Sum Problem

Posted on 2010-08-13
1
492 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

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.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

630 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