SQL转LINQ：多条件左连接与Outer Apply实现求助

问题描述 #

需要将包含多条件左连接（关联CompanyIndex和EmployeeATID）以及Outer Apply逻辑的SQL查询转换为LINQ，当前已完成部分转换，但核心关联逻辑未实现。

原SQL查询 #

string query = @"SELECT eventv3.[Index] AS ID,eventv3.EmployeeATID, 
  emp.LastName, emp.MidName, emp.FirstName, hd.Name AS DepartmentName, hp.Name AS PositionName,
  eventv3.RegisteredDate AS CreateDate, 
  CASE WHEN eventv3.[Status] = 0 THEN 'Waiting' WHEN eventv3.[Status]=2 THEN 'Deny' ELSE 'Approve' END AS [Status], 
  CASE WHEN details.Column1 NOT LIKE '0' THEN 'TongGio' ELSE 'VaoTreRaSom' END AS [Type], 
  details.column6 AS HasCardCount,details.column7 AS CalculateOT, 
  details.column8 AS AppliedInOffDay,details.column9 AS AppliedInHoliday, 
  details.Column1 AS TotalMinute,
  details.Column2 AS LateInMinute, details.Column3 AS EarlyOutMinute, 
  details.Column12 AS LateInEarlyOutType,
  eventv3.FromTime AS [From],eventv3.ToTime AS [To], 
  eventv3.Reason, eventv3.Note, 
  CASE WHEN eventv3.[Status]= 2 THEN approval.Reason ELSE '' END AS LyDoTuChoi,
  CASE WHEN eventv3.[Status]= 1 THEN approval.Reason ELSE '' END AS LyDoDongY,
  approval.Time AS ActionDate,
  empApprove.LastName AS ApprovalLastName, empApprove.MidName AS ApprovalMidName,
  empApprove.FirstName AS ApprovalFirstName, 
  empApprove.EmployeeATID AS ApprovalATID,
  empRegister.EmployeeATID AS RequestATID,
  empRegister.FirstName AS RequestFirstName,
  empRegister.MidName AS RequestMidName,
  empRegister.LastName AS RequestLastName,
  empNext.EmployeeATID AS NextATID,
  empNext.FirstName AS NextFirstName,
  empNext.MidName AS NextMidName,
  empNext.LastName AS NextLastName,
  details.Column18 AS NotifyEmail
  FROM PT_Event_Ver3 eventv3 
  LEFT JOIN HR_Employee emp ON emp.EmployeeATID= eventv3.EmployeeATID AND emp.CompanyIndex = eventv3.CompanyIndex
  LEFT JOIN HR_WorkingInfo AS hwi   ON hwi.EmployeeATID = emp.EmployeeATID AND hwi.CompanyIndex = emp.CompanyIndex
  AND DATEDIFF(DAY, hwi.FromDate, GETDATE()) >= 0 AND (hwi.ToDate IS NULL OR DATEDIFF(DAY, hwi.ToDate, GETDATE()) <= 0)
  LEFT JOIN HR_Department AS hd ON hd.[Index] = hwi.DepartmentIndex
  LEFT JOIN HR_Position hp ON hp.[Index] = hwi.PositionIndex
  LEFT JOIN HR_Employee empRegister ON empRegister.EmployeeATID = eventv3.RegisteredATID AND empRegister.CompanyIndex = eventv3.CompanyIndex
  LEFT JOIN  PT_EventDetail_Ver3 details ON details.EventIndex=eventv3.[Index] 
  OUTER APPLY(SELECT TOP 1 EmployeeATID,DoerATID, Reason, CompanyIndex, Time FROM PT_EventAction_Ver3  
  WHERE EventIndex= eventv3.[Index] AND CompanyIndex = @CompanyIndex ORDER BY [Time] DESC) AS approval 
  LEFT JOIN HR_Employee empApprove ON empApprove.EmployeeATID = approval.DoerATID AND empApprove.CompanyIndex =  approval.CompanyIndex
  LEFT JOIN HR_Employee empNext ON empNext.EmployeeATID = eventv3.NextUser AND empNext.CompanyIndex = eventv3.CompanyIndex
  WHERE PortalSystemFeature='DangKyVaoTreRaSom' AND eventv3.CancelEvent= @IsCancel 
  AND (eventv3.EmployeeATID = @EmployeeATID OR eventv3.RegisteredATID = @EmployeeATID) AND eventv3.CompanyIndex = @CompanyIndex 
  AND eventv3.[Index] < @IDPaging AND DATEDIFF(DAY, eventv3.FromTime, @ToDate) >= 0 AND DATEDIFF(DAY, eventv3.ToTime, @FromDate) <= 0 ";

当前未完成的LINQ代码 #

var result = (from eventv3 in DbContext.PT_Event_Ver3

 join emp in DbContext.HR_Employee
 on eventv3.EmployeeATID equals emp.EmployeeATID // AND nextApprover.CompanyIndex equals eventv3.CompanyIndex
 into emps
 from employee in emps.DefaultIfEmpty()

 join wif in DbContext.HR_WorkingInfo
 on employee.EmployeeATID equals wif.EmployeeATID // AND nextApprover.CompanyIndex equals eventv3.CompanyIndex
 into wifs
 from workingInfo in wifs.DefaultIfEmpty()

 join dep in DbContext.HR_Department
 on workingInfo.DepartmentIndex equals dep.Index into deps
 from department in deps.DefaultIfEmpty()

 join pos in DbContext.HR_Department
 on workingInfo.DepartmentIndex equals pos.Index into poses
 from position in poses.DefaultIfEmpty()

 join resEmp in DbContext.HR_Employee
 on eventv3.EmployeeATID equals resEmp.EmployeeATID // AND nextApprover.CompanyIndex equals eventv3.CompanyIndex
 into resEmps
 from registerEmployee in resEmps.DefaultIfEmpty()

 join evd in DbContext.PT_EventDetail_Ver3
 on eventv3.Index equals evd.EventIndex into evds
 from eventDetail in evds.DefaultIfEmpty()

 //Outer apply then left join

 join nxtEmp in DbContext.HR_Employee
 on eventv3.NextUser equals nxtEmp.EmployeeATID // AND nextApprover.CompanyIndex equals eventv3.CompanyIndex
 into nxtEmps
 from nextApprover in nxtEmps.DefaultIfEmpty()
 where eventv3.CompanyIndex == companyIndex
 && eventv3.PortalSystemFeature == "HuyDangKyVaoTreRaSom" && eventv3.CancelEvent == false
 && status.Contains(eventv3.Status ?? -1)
 && (eventv3.EmployeeATID == employeeATID || eventv3.RegisteredATID == employeeATID) && eventv3.CompanyIndex == CompanyIndex
    && eventv3.Index < IDPaging
 select new CancelLateInEarlyOutFullInfo
 {
     ID = eventv3.Index,
     EmployeeATID = eventv3.EmployeeATID,
     CreateDate = eventv3.RegisteredDate ?? DateTime.Now,
     Status = eventv3.Status.ToString(),
     From = eventv3.FromTime ?? DateTime.Now,
     To = eventv3.ToTime ?? DateTime.Now,
     PortalSystemFeature = eventv3.PortalSystemFeature,
     LateInMinute = eventDetail.Column3 ?? string.Empty,
     EarlyOutMinute = eventDetail.Column4 ?? string.Empty,
     TotalLateInEarlyOutMinute = eventDetail.Column5 ?? string.Empty,

     Note = eventv3.Note,
     Reason = eventv3.Reason,

     LastName = employee.LastName,
     MidName = employee.MidName,
     FirstName = employee.FirstName,
     DepartmentName = department.Name,

     RegistrationReason = eventDetail.Column2 ?? string.Empty,

     NextATID = nextApprover.EmployeeATID,
     NextLastName = nextApprover.LastName,
     NextMidName = nextApprover.MidName,
     NextFirstName = nextApprover.FirstName,
 }).OrderByDescending(t => t.ID).Take(pageSize).ToListAsync();

核心逻辑修正与完整LINQ实现 #

1. 多条件左连接处理 #

LINQ中实现多字段关联的左连接，需要用匿名类匹配所有关联键；对于SQL中ON后的附加过滤条件，需在DefaultIfEmpty()前通过Where筛选，避免转为全局WHERE过滤。

2. Outer Apply转换 #

SQL的OUTER APPLY (SELECT TOP 1 ... ORDER BY)对应LINQ中的FirstOrDefault()，结合OrderByDescending获取最新的一条记录。

3. 补全CASE逻辑与条件筛选 #

还原SQL中的CASE分支判断，以及原查询中的日期范围过滤条件。

完整LINQ代码如下：

var result = (from eventv3 in DbContext.PT_Event_Ver3
              // 多条件左连接HR_Employee（EmployeeATID + CompanyIndex）
              join emp in DbContext.HR_Employee
              on new { eventv3.EmployeeATID, eventv3.CompanyIndex } equals new { emp.EmployeeATID, emp.CompanyIndex }
              into emps
              from employee in emps.DefaultIfEmpty()

              // 多条件左连接HR_WorkingInfo，附加日期有效性判断
              join wif in DbContext.HR_WorkingInfo
              on new { employee.EmployeeATID, employee.CompanyIndex } equals new { wif.EmployeeATID, wif.CompanyIndex }
              into wifs
              from workingInfo in wifs
                  .Where(w => DbFunctions.DiffDays(w.FromDate, DateTime.Now) >= 0 && (w.ToDate == null || DbFunctions.DiffDays(w.ToDate, DateTime.Now) <= 0))
                  .DefaultIfEmpty()

              // 左连接HR_Department
              join dep in DbContext.HR_Department
              on workingInfo.DepartmentIndex equals dep.Index
              into deps
              from department in deps.DefaultIfEmpty()

              // 左连接HR_Position（修正原代码错误关联HR_Department的问题）
              join pos in DbContext.HR_Position
              on workingInfo.PositionIndex equals pos.Index
              into poses
              from position in poses.DefaultIfEmpty()

              // 多条件左连接HR_Employee（关联RegisteredATID + CompanyIndex）
              join resEmp in DbContext.HR_Employee
              on new { eventv3.RegisteredATID, eventv3.CompanyIndex } equals new { resEmp.EmployeeATID, resEmp.CompanyIndex }
              into resEmps
              from registerEmployee in resEmps.DefaultIfEmpty()

              // 左连接PT_EventDetail_Ver3
              join evd in DbContext.PT_EventDetail_Ver3
              on eventv3.Index equals evd.EventIndex
              into evds
              from eventDetail in evds.DefaultIfEmpty()

              // 模拟Outer Apply获取最新审批记录
              let approval = DbContext.PT_EventAction_Ver3
                  .Where(a => a.EventIndex == eventv3.Index && a.CompanyIndex == companyIndex)
                  .OrderByDescending(a => a.Time)
                  .FirstOrDefault()

              // 多条件左连接审批人信息
              join empApprove in DbContext.HR_Employee
              on new { DoerATID = approval?.DoerATID, approval?.CompanyIndex } equals new { empApprove.EmployeeATID, empApprove.CompanyIndex }
              into empApproves
              from approver in empApproves.DefaultIfEmpty()

              // 多条件左连接下一审批人
              join nxtEmp in DbContext.HR_Employee
              on new { eventv3.NextUser, eventv3.CompanyIndex } equals new { nxtEmp.EmployeeATID, nxtEmp.CompanyIndex }
              into nxtEmps
              from nextApprover in nxtEmps.DefaultIfEmpty()

              // 原SQL中的WHERE条件
              where eventv3.PortalSystemFeature == "DangKyVaoTreRaSom"
                    && eventv3.CancelEvent == IsCancel
                    && (eventv3.EmployeeATID == EmployeeATID || eventv3.RegisteredATID == EmployeeATID)
                    && eventv3.CompanyIndex == CompanyIndex
                    && eventv3.Index < IDPaging
                    && DbFunctions.DiffDays(eventv3.FromTime, ToDate) >= 0
                    && DbFunctions.DiffDays(eventv3.ToTime, FromDate) <= 0

              select new CancelLateInEarlyOutFullInfo
              {
                  ID = eventv3.Index,
                  EmployeeATID = eventv3.EmployeeATID,
                  CreateDate = eventv3.RegisteredDate ?? DateTime.Now,
                  // 还原Status的CASE判断
                  Status = eventv3.Status switch
                  {
                      0 => "Waiting",
                      2 => "Deny",
                      _ => "Approve"
                  },
                  From = eventv3.FromTime ?? DateTime.Now,
                  To = eventv3.ToTime ?? DateTime.Now,
                  PortalSystemFeature = eventv3.PortalSystemFeature,
                  // 对应原SQL的字段映射
                  LateInMinute = eventDetail?.Column2 ?? string.Empty,
                  EarlyOutMinute = eventDetail?.Column3 ?? string.Empty,
                  TotalLateInEarlyOutMinute = eventDetail?.Column1 ?? string.Empty,
                  HasCardCount = eventDetail?.Column6 ?? string.Empty,
                  CalculateOT = eventDetail?.Column7 ?? string.Empty,
                  AppliedInOffDay = eventDetail?.Column8 ?? string.Empty,
                  AppliedInHoliday = eventDetail?.Column9 ?? string.Empty,
                  LateInEarlyOutType = eventDetail?.Column12 ?? string.Empty,
                  NotifyEmail = eventDetail?.Column18 ?? string.Empty,

                  Note = eventv3.Note,
                  Reason = eventv3.Reason,

                  LastName = employee?.LastName,
                  MidName = employee?.MidName,
                  FirstName = employee?.FirstName,
                  DepartmentName = department?.Name,
                  PositionName = position?.Name,

                  // 审批相关字段
                  LyDoTuChoi = eventv3.Status == 2 ? approval?.Reason ?? string.Empty : string.Empty,
                  LyDoDongY = eventv3.Status == 1 ? approval?.Reason ?? string.Empty : string.Empty,
                  ActionDate = approval?.Time,
                  ApprovalATID = approver?.EmployeeATID,
                  ApprovalLastName = approver?.LastName,
                  ApprovalMidName = approver?.MidName,
                  ApprovalFirstName = approver?.FirstName,

                  // 申请人信息
                  RequestATID = registerEmployee?.EmployeeATID,
                  RequestFirstName = registerEmployee?.FirstName,
                  RequestMidName = registerEmployee?.MidName,
                  RequestLastName = registerEmployee?.LastName,

                  // 下一审批人信息
                  NextATID = nextApprover?.EmployeeATID,
                  NextLastName = nextApprover?.LastName,
                  NextMidName = nextApprover?.MidName,
                  NextFirstName = nextApprover?.FirstName,
              })
              .OrderByDescending(t => t.ID)
              .Take(pageSize)
              .ToListAsync();

关键说明 #

• 多条件关联必须使用匿名类确保所有键匹配，避免遗漏CompanyIndex导致关联错误；
• HR_WorkingInfo的日期条件需在DefaultIfEmpty()前筛选，保证是左连接的ON条件而非全局过滤；
• 使用let子句结合FirstOrDefault()实现OUTER APPLY的逻辑，获取最新的审批记录；
• 用C# 8.0+的switch表达式还原SQL中的CASE分支，代码更简洁；
• 所有可空字段需添加?空安全访问，避免空引用异常。

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