使用linq嵌套选择与组

本文关键字：选择 嵌套 linq 使用 | 更新日期: 2023-09-27 18:03:37

我正在尝试将以下查询转换为linQ表达式:

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
到目前为止，我做了以下操作: 
var statusCounts = models.GroupBy(x => new { x.CurrentStatus, x.LastChanged })
                         .Select(g => new { g.Key, Count = g.Count() })
                         .ToList();

有什么建议吗?

使用linq嵌套选择与组

为了有嵌套的选择，就像在你的sql，选择再次从model集合

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

或者在查询语法中:

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)
                   };
相关文章：