求对应SQL Server UNBOUNDED PRECEDING窗口函数的C# LINQ查询

原始数据表 #

目标SQL查询 #

SELECT [Date],
        SUM(EarnedMHRS) OVER(ORDER BY DayVal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) As EarnedMHRS,
        Round(SUM(MHRS) OVER(ORDER BY DayVal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),0) AS MHRS, 
        per      
        FROM TblPanning;

尝试的LINQ代码（未成功） #

decimal? runningTotal = 0;
decimal? runningTotalMHRS = 0;
var tmpOutput2 = tmpOutput1.Select(item =>
     {
         runningTotal += item.EarnedMHRS;
         runningTotalMHRS += item.MHRS;
         return new
         {
             item.Date,
             MHRS = item.DayVal == totalDays ? totallMhrs : Math.Round(runningTotalMHRS ?? 0, 0),
             item.per,
             EarnedMHRS = runningTotal,
         };
     });

期望输出结果 #

解决方案 #

你的LINQ代码存在两个核心问题：一是未正确处理EarnedMHRS的null值，二是多余的条件判断干扰了累计逻辑。修正后的代码如下：

// 先按DayVal排序，对齐SQL中的ORDER BY逻辑
var sortedData = tmpOutput1.OrderBy(item => item.DayVal).ToList();

decimal runningTotalEarned = 0;
decimal runningTotalMHRS = 0;

var tmpOutput2 = sortedData.Select(item =>
{
    // 处理EarnedMHRS的null值，null时按0累加
    runningTotalEarned += item.EarnedMHRS ?? 0;
    // 累加MHRS
    runningTotalMHRS += item.MHRS;
    
    return new
    {
        item.Date,
        EarnedMHRS = runningTotalEarned,
        MHRS = Math.Round(runningTotalMHRS, 0),
        item.per
    };
});

关键说明 #

1. 必须先排序：SQL窗口函数依赖ORDER BY DayVal确定累计顺序，LINQ中需要先对数据排序，否则累计结果会混乱。
2. null值处理：原始数据中大部分EarnedMHRS为null，用item.EarnedMHRS ?? 0确保累计时不会出现异常，且结果始终为766。
3. 移除多余条件：SQL中没有item.DayVal == totalDays的逻辑，直接对累计的runningTotalMHRS取整即可对齐目标结果。

内容的提问来源于stack exchange，提问作者avnish maddheshiya