CODEWITHSUNDEEP
c#linqselectgroup-by
Hanady
Hanady

Reputation: 789

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?

Upvotes: 1

Views: 616

Answers (1)

Gilad Green
Gilad Green

Reputation: 37281

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)
                   };

Upvotes: 1

Related Questions