SQL转Linq求助：将含多列聚合的SQL语句转换为Linq语句

查询语法（贴近原SQL结构，适合新手理解） #

var result = from r in db.tbl_LeaveRequested
             join a in db.tbl_LeaveAllocation on r.LeaveTypeID equals a.LeaveTypeID
             join t in db.tbl_LeaveType on a.LeaveTypeID equals t.LeaveTypeID
             where r.LeaveStatus != "Rejected"
             group r by new { r.Id, a.LeaveTypeID, t.LeaveType } into g
             select new {
                 emp = g.Key.Id,
                 leave = g.Key.LeaveTypeID,
                 g.Key.LeaveType,
                 TotalDays = g.Sum(x => x.NumDaysRequested),
                 TotalHours = g.Sum(x => x.NumHoursRequested),
                 TotalMins = g.Sum(x => x.NumMinsRequested)
             };

方法语法（链式调用风格） #

var result = db.tbl_LeaveRequested
    .Where(r => r.LeaveStatus != "Rejected")
    .Join(db.tbl_LeaveAllocation,
          r => r.LeaveTypeID,
          a => a.LeaveTypeID,
          (r, a) => new { r, a })
    .Join(db.tbl_LeaveType,
          ra => ra.a.LeaveTypeID,
          t => t.LeaveTypeID,
          (ra, t) => new { ra.r, ra.a, t })
    .GroupBy(x => new { x.r.Id, x.a.LeaveTypeID, x.t.LeaveType })
    .Select(g => new {
        emp = g.Key.Id,
        leave = g.Key.LeaveTypeID,
        g.Key.LeaveType,
        TotalDays = g.Sum(x => x.r.NumDaysRequested),
        TotalHours = g.Sum(x => x.r.NumHoursRequested),
        TotalMins = g.Sum(x => x.r.NumMinsRequested)
    });

核心要点说明 #

• 内连接通过join关键字（查询语法）或Join方法（方法语法）实现，匹配逻辑对应原SQL的ON子句
• where子句过滤掉状态为Rejected的请求记录
• 分组时用匿名类封装所有需要分组的字段，对应原SQL的GROUP BY列表
• 聚合计算使用Sum方法，分别统计三个时长字段的总和
• 最终通过匿名类返回与原SQL查询结果结构一致的字段

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