C#中LINQ多表连接问题求助：新增TnBranch表连接失败

你的问题出在第二次Join的参数写法上，具体错误有两处：

• 第二次Join的外键匹配逻辑写错：第一个Join返回的是包含tnWhatsapp和tenant的匿名对象，你需要从这个匿名对象里取tnWhatsapp.BranchId来关联TnBranch.Id
• Join的结果选择器参数数量错误：两次Join的结果选择器只接收两个参数——前一次Join的结果对象、当前Join匹配到的TnBranch对象

修正后的代码 #

var query = _saasdbContext.TnWhatsapp.AsNoTracking();

if (!isSAASAdmin)
    query = query.Where(x => x.TenantId == tenantID);

int totalCount = await query.CountAsync();
query = QueryableExtensions.QueryOrderBy(query, sortList);
List<TnWhatsappMessage> whatsappDedicatedListForTenant = new List<TnWhatsappMessage>();

if (take == 0)
    whatsappDedicatedListForTenant = await query.Skip(skip)
                                    .Join(_saasdbContext.Tenant.AsNoTracking(),
                                        tnWhatsapp => tnWhatsapp.TenantId,
                                        tenant => tenant.Id,
                                        (tnWhatsapp, tenant) => new { tnWhatsapp, tenant }) // 保留第一次Join的结果
                                    .Join(_saasdbContext.TnBranch.AsNoTracking(),
                                        joinResult => joinResult.tnWhatsapp.BranchId, // 从匿名对象中取BranchId
                                        branch => branch.Id,
                                        (joinResult, branch) => new TnWhatsappMessage() // 参数为前一次结果和匹配到的Branch
                                        {
                                            Id = joinResult.tnWhatsapp.Id,
                                            TenantId = joinResult.tnWhatsapp.TenantId,
                                            BranchId = joinResult.tnWhatsapp.BranchId,
                                            InstanceId = joinResult.tnWhatsapp.InstanceId,
                                            Token = joinResult.tnWhatsapp.Token,
                                            TenantName = joinResult.tenant.CompanyName,
                                            BranchName = branch.Code,
                                            DayLimit = joinResult.tnWhatsapp.DayLimit,
                                            MinuteLimit = joinResult.tnWhatsapp.MinuteLimit,
                                        })
                                    .ToListAsync();

关键修改说明 #

1. 第二次Join的外键选择器改为joinResult => joinResult.tnWhatsapp.BranchId，明确从第一次Join返回的匿名对象中获取TnWhatsapp的BranchId
2. 结果选择器参数调整为(joinResult, branch)，其中joinResult包含第一次Join的tnWhatsapp和tenant对象，branch是匹配到的TnBranch记录
3. 映射TnWhatsappMessage属性时，通过joinResult.tnWhatsapp和joinResult.tenant访问对应字段

可选：左连接支持（允许BranchId为空） #

如果存在TnWhatsapp的BranchId为null的情况，需要用左连接保留这些记录，写法如下：

if (take == 0)
    whatsappDedicatedListForTenant = await query.Skip(skip)
                                    .Join(_saasdbContext.Tenant.AsNoTracking(),
                                        tnWhatsapp => tnWhatsapp.TenantId,
                                        tenant => tenant.Id,
                                        (tnWhatsapp, tenant) => new { tnWhatsapp, tenant })
                                    .GroupJoin(_saasdbContext.TnBranch.AsNoTracking(),
                                        joinResult => joinResult.tnWhatsapp.BranchId,
                                        branch => branch.Id,
                                        (joinResult, branches) => new { joinResult, branches })
                                    .SelectMany(x => x.branches.DefaultIfEmpty(),
                                        (x, branch) => new TnWhatsappMessage()
                                        {
                                            Id = x.joinResult.tnWhatsapp.Id,
                                            TenantId = x.joinResult.tnWhatsapp.TenantId,
                                            BranchId = x.joinResult.tnWhatsapp.BranchId,
                                            InstanceId = x.joinResult.tnWhatsapp.InstanceId,
                                            Token = x.joinResult.tnWhatsapp.Token,
                                            TenantName = x.joinResult.tenant.CompanyName,
                                            BranchName = branch?.Code, // 空值处理
                                            DayLimit = x.joinResult.tnWhatsapp.DayLimit,
                                            MinuteLimit = x.joinResult.tnWhatsapp.MinuteLimit,
                                        })
                                    .ToListAsync();

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