How can I convert this SQL Query into LINQ (OVER (PARTITION BY Date))

Question

Here's the query I'm trying to convert into Linq:

SELECT R.Code, 
       R.FlightNumber, 
       S.[Date], 
       S.Station,
       R.Liters, 
       SUM(R.Liters) OVER (PARTITION BY Year([Date]), Month([Date]), Day([Date])) AS Total_Liters
FROM S INNER JOIN
               R ON S.ID = R.SID
WHERE (R.Code = 'AC')
AND FlightNumber = '124'
GROUP BY  Station, Code, FlightNumber, [Date], Liter
ORDER BY R.FlightNumber, [Date]

Thanks for any help.

UPDATE: Here is the Linq code I'm trying it on; I cannot make the OVER PARTITION by Date.

var test = 
(from record in ent.Records join ship in ent.Ship on record.ShipID equals ship.ID                       

orderby ship.Station
where ship.Date > model.StartView && ship.Date < model.EndView && ship.Station == model.Station && record.FlightNumber == model.FlightNumber

group record by new {ship.Station, record.Code, record.FlightNumber, ship.Date, record.AmountType1} into g

select new { g.Key.Station, g.Key.Code, g.Key.FlightNumber, g.Key.Date, AmmountType1Sum = g.Sum(record => record.AmountType1) });

What have you tried so far? Stackoverflow isn't a site where you just post something and tell people "Fix it" or "Convert this", so in the future when you ask a question, please think of that. — Joakim, Jun 30 '12 at 21:16
In my experience, I have found linqer to be a really useful tool for converting t-sql to linq. http://www.sqltolinq.com/ - Comes with a 10 day free trial. — Jeremy Wiggins, Jun 30 '12 at 22:10
@JeremyWiggins OVER PARTITION isn't recognized in linqer i'm affraid.. — Martin Gemme, Jun 30 '12 at 22:24
You can do it easily in two steps. Execute query without SUM and calculate SUM on client side. Client means .NET code. — LukLed, Jun 30 '12 at 23:27
@LukLed I tried for hours of doing this. I tried creating a rollup function and I cannot make it done. I want something to sum by date (e.g. Over Partition By Date). — Martin Gemme, Jun 30 '12 at 23:44

score 5 · Accepted Answer · answered Jun 30 '12 at 23:57

Execute query first without aggregation:

var test = 
(from record in ent.Records join ship in ent.Ship on record.ShipID equals ship.ID                       

orderby ship.Station
where ship.Date > model.StartView && ship.Date < model.EndView && ship.Station == model.Station && record.FlightNumber == model.FlightNumber

select new {ship.Station, record.Code, record.FlightNumber, ship.Date, record.AmountType1};

Then calculate sum

var result = 
    from row in test
    select new {row.Station, row.Code, row.FlightNumber, row.Date, row.AmountType1, 
    AmountType1Sum = test.Where(r => r.Date == row.Date).Sum(r => r.AmountType1) };

This should produce the same effect as database query. Code above may contain errors, because I wrote it only here.

score 1 · Answer 2 · edited May 23 '17 at 10:26

I've answered a similar thread on: LINQ to SQL and a running total on ordered results

On that thread it was like this:

var withRuningTotals = from i in itemList    
                   select i.Date, i.Amount,    
                          Runningtotal = itemList.Where( x=> x.Date == i.Date).
                                                  GroupBy(x=> x.Date).
                                                  Select(DateGroup=> DateGroup.Sum(x=> x.Amount)).Single();

In you situation, you might have to join the two tables together first while grouping, then run the same concept above on the joined table result.

How can I convert this SQL Query into LINQ (OVER (PARTITION BY Date))

2 Answers2