如何将带左连接子查询的SQL Server语句转换为LINQ？

先拆解原SQL逻辑 #

原SQL核心是：UserApplicationAccess与MasterApplication内连，再左连一个由ApplicationRole、MasterApplication、UserRole关联而成的子查询，最后对左连后的空字段替换默认值。

实现步骤1：先定义子查询（对应原SQL的x子查询） #

var subQuery = from a in context.ApplicationRole.AsNoTracking()
               join m in context.MasterApplication.AsNoTracking() on a.ApplicationId equals m.Id
               join u in context.UserRole.AsNoTracking() on a.Id equals u.RoleId
               select new 
               {
                   a.Id,
                   a.Code,
                   a.Name,
                   AppId = m.Id,
                   u.UserCode
               };

实现步骤2：主查询+左连接逻辑 #

用LINQ的join ... into ...配合DefaultIfEmpty()实现SQL的LEFT JOIN，再用??处理空值替换：

// 替换为实际的用户编码参数
var targetUserCode = usercode;

var result = from ua in context.UserApplicationAccess.AsNoTracking()
             // 内连MasterApplication
             join ma in context.MasterApplication.AsNoTracking() on ua.ApplicationID equals ma.Id
             // 左连子查询，多条件连接用匿名类型匹配
             join x in subQuery 
             on new { ua.Usercode, ua.ApplicationID } equals new { x.UserCode, x.AppId } 
             into leftJoinedData
             // 左连的核心：DefaultIfEmpty()表示允许左连结果为空
             from x in leftJoinedData.DefaultIfEmpty()
             // 映射到DTO，空值替换默认值
             select new ApplicationRoleDTO2
             {
                 Id = x?.Id ?? 0,
                 Code = x?.Code ?? "DEFAULT",
                 Name = x?.Name ?? "DEFAULT"
             };

List<ApplicationRoleDTO2> UserRoles = result.ToList();

简化写法（子查询嵌入主查询） #

如果不想单独定义子查询，也可以直接把逻辑嵌入主查询：

var targetUserCode = usercode;

var result = from ua in context.UserApplicationAccess.AsNoTracking()
             join ma in context.MasterApplication.AsNoTracking() on ua.ApplicationID equals ma.Id
             join x in (
                 from a in context.ApplicationRole.AsNoTracking()
                 join m in context.MasterApplication.AsNoTracking() on a.ApplicationId equals m.Id
                 join u in context.UserRole.AsNoTracking() on a.Id equals u.RoleId
                 select new 
                 {
                     a.Id,
                     a.Code,
                     a.Name,
                     AppId = m.Id,
                     u.UserCode
                 }) 
             on new { ua.Usercode, ua.ApplicationID } equals new { x.UserCode, x.AppId } into leftJoinedData
             from x in leftJoinedData.DefaultIfEmpty()
             select new ApplicationRoleDTO2
             {
                 Id = x?.Id ?? 0,
                 Code = x?.Code ?? "DEFAULT",
                 Name = x?.Name ?? "DEFAULT"
             };

List<ApplicationRoleDTO2> UserRoles = result.ToList();

关键注意点 #

• LINQ实现LEFT JOIN的核心是join ... into ... + from ... DefaultIfEmpty()，对应SQL的LEFT JOIN逻辑
• 多条件连接时，必须用匿名类型匹配字段，且两边的字段名、数据类型要完全一致
• C#的??运算符等价于SQL的ISNULL，用来处理空值替换默认值
• 保留AsNoTracking()可以避免EF跟踪实体，提升查询性能，和你原代码的思路一致

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