Hanady Hanady - 3 months ago 21
C# Question

Nested select with group by using linq

I am trying to convert the following query to a linQ expression:

SELECT t1.lastchanged,
currentstatus,
(SELECT count(ID)
FROM [Issues] t2
WHERE t2.CurrentStatus = t1.currentstatus and t2.lastchanged <= t1.lastchanged) AS running_total
FROM [Issues] t1
GROUP BY t1.lastchanged, currentstatus
ORDER BY t1.lastchanged


So far, I did the following:

var statusCounts = models.GroupBy(x => new { x.CurrentStatus, x.LastChanged })
.Select(g => new { g.Key, Count = g.Count() })
.ToList();


Any suggestions?

Answer

In order to have the nested select, just like in your sql, select again from the model collection.

var statusCounts = models.GroupBy(x => new { x.CurrentStatus, x.LastChanged })
                         .Select(g => new 
                         { 
                             CurrentStatus = g.Key.CurrentStatus, 
                             LastChanged = g.Key.LastChanged,
                             Count = models.Count(m => m.CurrentStatus == g.Key.CurrentStatus &&
                                                       m.LastChanged  <= g.Key.LastChanged)
                         })
                         .OrderBy(item => item.Key.LastChanged);

Or in query syntax:

var statusCounts = from t1 in models
                   group t1 by new { x.CurrentStatus, x.LastChanged } into g
                   orderby g.Key.LastChanged
                   select new 
                   { 
                       CurrentStatus = g.Key.CurrentStatus, 
                       LastChanged = g.Key.LastChanged,
                       Count = models.Count(m => m.CurrentStatus == g.Key.CurrentStatus &&
                                                 m.LastChanged  <= g.Key.LastChanged)
                   };