c# - LINQ:内连接2表+外连接1表

Question

我有 2 个内部联接(3 个表)，但我不知道，而且我发现很难在 LINQ 中实现我对外部联接的研究。如何将最后一个内部连接更改为外部连接，这样即使列(角色)为空，列仍将连接？

这是我想转换为 LINQ 的现有 SQL 版本:

SELECT dbo.EmployeeAccess.id, dbo.EmployeeAccess.EmpNo, dbo.EmployeeAccess.RoleID, dbo.EmployeeAccess.Active, dbo.EmployeeAccessLevel.Role, 
                  dbo.View_HCM.LNameByFName

FROM  dbo.EmployeeAccess LEFT OUTER JOIN
      dbo.EmployeeAccessLevel ON dbo.EmployeeAccess.RoleID = dbo.EmployeeAccessLevel.id INNER JOIN
      dbo.View_HCM ON dbo.EmployeeAccess.EmpNo = dbo.View_HCM.EmpNo

我现在有 2 个内部联接的 LINQ:

    (from ea in context.EmployeeAccesses
                    join vh in context.View_HCM on (Int16)ea.EmpNo equals vh.EmpNo
                    join rl in context.EmployeeAccessLevels on ea.RoleID equals rl.id 
                    select new EmployeeWithEmail{

                        EmpNum = ea.EmpNo ?? 0,
                        EmailAddress = vh.EmailAddress,                          
                        LNameByFname = vh.LNameByFName,                                  
                        Active2 = ea.Active ?? false

                    }).ToList();
        }

Answer 1

Linq 的外连接语法使用两部分。首先是 into 然后是 DefaultIfEmpty

在您的情况下，外部联接可能如下所示:

(from ea in context.EmployeeAccesses
join vh in context.View_HCM on (Int16)ea.EmpNo equals vh.EmpNo
join rl in context.EmployeeAccessLevels on ea.RoleID equals rl.id into outer_join
from subjoin in outer_join.DefaultIfEmpty()
select new EmployeeWithEmail{

    EmpNum = ea.EmpNo ?? 0,
    EmailAddress = vh.EmailAddress,                          
    LNameByFname = vh.LNameByFName,                                  
    Active2 = ea.Active ?? false

}).ToList();

有many tutorials关于如何在 LINQ 中创建外部联接。