将含ROW_NUMBER() OVER PARTITION的SQL查询转换为C# LINQ子查询

先拆解原SQL的核心逻辑：

• 筛选出最近181天内、类别为「Attendance」的员工辅导记录
• 按员工GUID分组（对应SQL的PARTITION BY），每组内按辅导日期、记录ID排序，给每条记录分配行号
• 只保留每组里行号为1的第一条记录

分步实现LINQ #

1. 先获取目标类别ID

先拿到描述为"Attendance"的CounselCategory的ID，避免主查询重复执行子查询：

var attendanceCategoryId = dbContext.CounselCategory
    .Where(cc => cc.Description == "Attendance")
    .Select(cc => cc.Id)
    .FirstOrDefault();

2. 主查询实现分区行号逻辑

提供两种常见写法，按需选择：

方法语法（链式调用）

var result = dbContext.Counsel
    .Where(c => c.CounselDate > DateTime.Today.AddDays(-181) 
                && c.CategoryId == attendanceCategoryId)
    .GroupBy(c => c.EmployeeGUID)
    .SelectMany(g => g.OrderBy(c => c.CounselDate)
                      .ThenBy(c => c.CounselId)
                      .Select((c, index) => new 
                      {
                          c.CounselId,
                          c.EmployeeGUID,
                          c.CounselDate,
                          RowPartition = index + 1 // 索引从0开始，加1对应SQL的行号
                      }))
    .Where(item => item.RowPartition == 1)
    .Select(item => new 
    {
        item.CounselId,
        item.EmployeeGUID,
        item.CounselDate
    })
    .ToList();

查询语法（接近SQL结构）

var result = from c in dbContext.Counsel
             where c.CounselDate > DateTime.Today.AddDays(-181) 
                   && c.CategoryId == attendanceCategoryId
             group c by c.EmployeeGUID into g
             from sortedItem in g.OrderBy(x => x.CounselDate).ThenBy(x => x.CounselId)
                                .Select((x, idx) => new { x, RowPartition = idx + 1 })
             where sortedItem.RowPartition == 1
             select new 
             {
                 sortedItem.x.CounselId,
                 sortedItem.x.EmployeeGUID,
                 sortedItem.x.CounselDate
             };

关键对应说明 #

• GroupBy(c => c.EmployeeGUID)等价于SQL的PARTITION BY EmployeeGUID
• Select((c, index) => ...)中的index是分组内的排序索引，加1后匹配SQL的ROW_NUMBER()结果
• DateTime.Today.AddDays(-181)对应SQL的DATEADD(DAY, -181, CAST(GETDATE() AS DATE))，确保只按日期部分计算

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