如何将这些 LINQ 连接转换为 LEFT OUTER 连接？

Question

var auditAgencyRecords = (from ag in db.Agencies
                                      join ara in db.AuditRuleAccounts on ag.Agency_Id equals ara.AgencyID
                                      join arr in db.AuditRuleResults on ara.AuditRuleAccountID equals arr.AuditRuleAccountID
                                      join are in db.AuditRuleEnterprises on arr.AuditRuleEnterpriseID equals are.AuditRuleEnterpriseID
                                      where (rules.Select(r => r.EnterpriseID).Contains(arr.AuditRuleEnterpriseID))
                                      select new
                                      {

                                          AgencyID = ag.Agency_Id,
                                          AgencyName = ag.Agency_Name,
                                          AuditRuleEnterpriseID = arr.AuditRuleEnterpriseID,
                                          AuditRuleEnterpriseName = are.OverrideDisplayName,
                                          CorrectedDate = arr.CorrectedDate,
                                          NbrDaysToCorrect = arr.NbrDaysToCorrect,

                                      });

因此，我对 LINQ to SQL 还很陌生，我需要帮助弄清楚如何将其转换为左外连接，而不是内连接。

从上面的查询中，我希望获得机构表中的所有机构，然后右侧的记录（EnterpriseID、CorretedDate 等）可以为空，如果不存在记录，则以此类推。

我相当确定我需要使用 SelectMany，但我可以使用一些指导将这些联接转换为左外联接，因此我有一个所有机构的列表，然后在右侧列出了它们的可能记录。

Answer 1

下面是一些可以帮助您入门的代码。

var auditAgencyRecords = (
from ag in db.Agencies
group join ara in db.AuditRuleAccounts on ag.Agency_Id equals ara.AgencyID into AgAra = group
from w in AgAra.DefaultIfEmpty()
group join arr in db.AuditRuleResults on ara.AuditRuleAccountID equals arr.AuditRuleAccountID into AraArr = group
from x in AraArr.DefaultIfEmpty()
group join are in db.AuditRuleEnterprises on arr.AuditRuleEnterpriseID equals are.AuditRuleEnterpriseID into ArrAre = group
from y in ArrAre.DefaultIfEmpty()
where (rules.Select(r => r.EnterpriseID).Contains(arr.AuditRuleEnterpriseID))
select new
{    
     AgencyID = w.Agency_Id,
     AgencyName = w.Agency_Name,                                           
     AuditRuleEnterpriseID = y.AuditRuleEnterpriseID,
     AuditRuleEnterpriseName = y.OverrideDisplayName,
     CorrectedDate = w.CorrectedDate,
     NbrDaysToCorrect = w.NbrDaysToCorrect,
});

Answer 2

这里是如何执行此操作的一个很好的示例。
重要的是不要忘记这些可能带回来的空值——这曾经让我很难过。