How to convert SQL query to Linq

Question

How can we convert SQL query below to linq (C#)?

SELECT o.Week,
        (select sum(Positive-Negative)
        FROM tbl i
        where i.Week <= o.Week
        )   
    FROM tbl o

I have tried but i am not sure how to sum up these values

var result = from i in weeklySatisfaction
from o in weeklySatisfaction
where i.Week <= o.Week
select new
{
   Week = o.Week,
   Net = i.Positive - i.Negative
};

Your original SQL query uses a "correlated subquery" (i.e. another query directly inside the `SELECT` clause. This is inefficient. You should use a `JOIN` or other approach instead. — Dai, Feb 09 '17 at 06:23
Your original query seems to be using a very inefficient method for computing a running sum - a more efficient way in SQL is to use `OVER`, however Linq does not support this directly, and there is a very different approach for computing running sums in Linq, see here: http://stackoverflow.com/questions/1834753/linq-to-sql-and-a-running-total-on-ordered-results — Dai, Feb 09 '17 at 06:26
...so in conclusion: your SQL query is not directly translatable to Linq, which explains your difficulties. — Dai, Feb 09 '17 at 06:27
You don't mention what type of LINQ you're using. And you should show the class model so we can see how entities are related. — Gert Arnold, Feb 10 '17 at 19:12

score 0 · Answer 1 · answered Feb 10 '17 at 18:11

This linq code will work

    var values = weeklySatisfaction.Select(o => new 
   { 
     Week = o.Week, 
     Net = weeklySatisfaction
                 .Where(i => i.Week < o.Week)
                 .Select(x => x.Positive - x.Negative).Sum() 
   });

Enjoy !

How to convert SQL query to Linq

1 Answers1