Convert from SQL query to LINQ syntax with outer apply and left join

Question

I need your help. I have one SQL query, I want to convert to LINQ syntax but I don't know outer apply and left join with multi equals.

I have this SQL query :

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 ";

and I converted new LINQ query, but I don't know how to use multi equals left join (because forgein key is CompanyIndex and EmployeeATID) and outer apply then left join with HR_Employee table from data of outer apply record:

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();

Any help is appreciated, thanks you!

Answer 1

You can add the following to your query, it is the equivalent of your OUTER APPLY

from approval in DbContext.PT_EventAction_Ver3.Where(evt3 =>
    evt3.Index == details.EventIndex &&
    evt3.CompanyIndex == companyIndex
  ).OrderByDescending(evt3 => evt3.Time).FirstOrDefault().DefaultIfEmpty();

Remove the DefaultIfEmpty() to do a CROSS APPLY