使用 GroupBy 获取员工姓名、员工 ID、使用 LINQ 按类别支付的总和

Question

List<ps_Employee> _Employees = Employee.GetListOfActiveEmployees();
List<ps_Detail> _Details  = Detail.GetListOfDetails();

var _RowDetails = from _Detail in _Details
                  join _Employee in _Employees on _Detail.EmployeeCode equals _Employee.EmployeeCode select new { Employee = _Employee, Detail = _Detail } into EmployeePayDetail
                  group EmployeePayDetail by EmployeePayDetail.Detail.EmployeeCode into x
                  select new
                  {
                      EmployeeCode = x.Key,
                      BasicPay = x.Where(i => i.Detail.ComponentCode.Equals("PE01")).Sum(a => a.Detail.Amount),
                      OtherIncome = x.Where(i => i.Detail.ComponentCode.StartsWith("PE")
                                            && !i.Detail.ComponentCode.Equals("PE01")).Sum(a => a.Detail.Amount),
                      Deductions = x.Where(i => i.Detail.ComponentCode.StartsWith("PD")).Sum(a => a.Detail.Amount),
                      GrossPay = x.Where(i => i.Detail.ComponentCode.StartsWith("PE")).Sum(a => a.Detail.Amount),
                      PAYE = x.Where(i => i.Detail.ComponentCode.Equals("PD03")).Sum(a => a.Detail.Amount),
                      NetPay = x.Where(i => i.Detail.ComponentCode.Equals("PE01")).Sum(a => a.Detail.Amount)
                  };

通过循环，我可以在没有员工表中包含的员工姓名的情况下获得下面的信息，

employeeCode EmployeeName PEO1    PD 
x001           ?????      200.00  400.00

如何从上面获取员工姓名？

Answer 1

我将按您实际需要的 EmployeePayDetail.Employee 进行分组，然后：

select new
{
    EmployeeCode = x.Key.EmployeeCode,
    EmployeeName = x.Key.EmployeeName,
    BasicPay = x.Where(i => i.Detail.ComponentCode.Equals("PE01")).Sum(a => a.Detail.Amount),
    ...