C# 多表联查、InnerJoin、LeftJoin相关问题

案例一：

Linq代码如下：

var loginUser = (from u in _systemUserRepository.Table
                             join ur in _systemUserRoleRepository.Table on u.Id equals ur.UserId
                             join r in _systemRoleRepository.Table on ur.RoleId equals r.Id
                             join ula in _systemUserRelaRepository.Table on u.Id equals ula.UserId
                             where u.Account == account && u.Password == password
                             select new SystemUserLoginDTO()
                             {
                                 Id = u.Id,
                                 Account = u.Account,
                                 Name = u.Name,
                                 RoleId = r.Id,
                                 RoleType = r.RoleType,
                                 EmployeeId = ula.RelaId
                             }).FirstOrDefault();

生成如下SQL脚本：

SELECT 
    [Limit1].[RoleType] AS [RoleType], 
    [Limit1].[Id] AS [Id], 
    [Limit1].[Account] AS [Account], 
    [Limit1].[Name] AS [Name], 
    [Limit1].[Id1] AS [Id1], 
    [Limit1].[RelaId] AS [RelaId]
    FROM ( SELECT TOP (1) 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Account] AS [Account], 
        [Extent1].[Name] AS [Name], 
        [Extent3].[Id] AS [Id1], 
        [Extent3].[RoleType] AS [RoleType], 
        [Extent4].[RelaId] AS [RelaId]
        FROM    [dbo].[SystemUser] AS [Extent1]
        INNER JOIN [dbo].[SystemUserRole] AS [Extent2] ON [Extent1].[Id] = [Extent2].[UserId]
        INNER JOIN [dbo].[SystemRole] AS [Extent3] ON [Extent2].[RoleId] = [Extent3].[Id]
        INNER JOIN [dbo].[SystemUserRela] AS [Extent4] ON [Extent1].[Id] = [Extent4].[UserId]
        WHERE [Extent1].[Account] = 'admin' AND [Extent1].[Password] = 'd42683b3df678c61'
    )  AS [Limit1]

案例二：

Linq代码如下：

 var empDept = (from e in _employeeRepository.Table
                                   join ul in _systemUserRelaRepository.Table on e.Id equals ul.RelaId
                                   join d in _departmentRepository.Table on e.DeptId equals d.Id
                                   join p in _positionRepository.Table on e.PositionId equals p.Id into pJoin
                                   from pTemp in pJoin.DefaultIfEmpty()
                                   where ul.UserId == loginUser.Id && ul.RelaTableName == "HREmployee"
                                   select new EmployeeDeptDTO()
                                   {
                                       DeptId = d.Id,
                                       DeptName = d.DeptName,
                                       DeptCode = d.DeptCode,
                                       PositionId = pTemp.Id,
                                       PositionName = pTemp.Name
                                   }).FirstOrDefault();

生成如下SQL脚本：

SELECT 
    [Limit1].[C1] AS [C1], 
    [Limit1].[Id] AS [Id], 
    [Limit1].[DeptName] AS [DeptName], 
    [Limit1].[DeptCode] AS [DeptCode], 
    [Limit1].[Id1] AS [Id1], 
    [Limit1].[Name] AS [Name]
    FROM ( SELECT TOP (1) 
        [Filter1].[Id1] AS [Id], 
        [Filter1].[DeptCode] AS [DeptCode], 
        [Filter1].[DeptName] AS [DeptName], 
        1 AS [C1], 
        [Extent4].[Id] AS [Id1], 
        [Extent4].[Name] AS [Name]
        FROM   (SELECT [Extent1].[PositionId] AS [PositionId], [Extent2].[UserId] AS [UserId], [Extent3].[Id] AS [Id1], [Extent3].[DeptCode] AS [DeptCode], [Extent3].[DeptName] AS [DeptName]
            FROM   [dbo].[HREmployee] AS [Extent1]
            INNER JOIN [dbo].[SystemUserRela] AS [Extent2] ON [Extent1].[Id] = [Extent2].[RelaId]
            INNER JOIN [dbo].[HRDepartment] AS [Extent3] ON [Extent1].[DeptId] = [Extent3].[Id]
            WHERE 'HREmployee' = [Extent2].[RelaTableName] ) AS [Filter1]
        LEFT OUTER JOIN [dbo].[HRPosition] AS [Extent4] ON [Filter1].[PositionId] = [Extent4].[Id]
        WHERE ([Filter1].[UserId] = '1489681308820001') OR (([Filter1].[UserId] IS NULL) AND ('1489681308820001' IS NULL))
    )  AS [Limit1]

结论：

1、是否左连接，在于是否使用了 DefaultIfEmpty() 方法，使用了该方法，那么就是一次左连接。不使用该方法时，就是内连接。

2、在使用 DefaultIfEmpty() 方法之前 join 的 表会先内连接查询作为一个整体作为左连接主体，来跟使用了 DefaultIfEmpty() 方法的表进行左连接。