DefaultIfEmpty()不处理空集合

Question

我一直在尝试左加入表，他们是一对多的关系。

我已经编写了一个SQL查询，并试图将其转换为我的ASP.NET核心应用程序的LINQ。

我的sql查询如下：

    SELECT ap.SystemId, 
           ap.AccessRequiredToId, 
           cb.AccessAreaManagementId, 
           ap.EquipmentTagId, 
           COUNT(ap.Name) [Count] 
      FROM ApplicationForms ap LEFT JOIN AccessAreaCheckBoxes cb 
        ON n ap.RecordId = cb.RecordId
     WHERE EndDate IS NULL AND (Checked IS NULL OR Checked = 1)
  GROUP BY ap.SystemId, ap.AccessRequiredToId, cb.AccessAreaManagementId, ap.EquipmentTagId

SQL Result

我的LINQ如下：

var active = _context.ApplicationForms
                .Where(w => w.EndDate == null)
                .GroupJoin(_context.AccessAreaCheckBoxes
                .Where(w => (w.AccessAreaManagement == null || w.Checked == true)),
                x => x.RecordId,
                y => y.RecordId,
                (x, y) => new { ApplicationForms = x, AccessAreaCheckBoxes = y })
                .SelectMany(x => x.AccessAreaCheckBoxes.DefaultIfEmpty(),
                (x, y) => new { x.ApplicationForms, AccessAreaCheckBoxes = y })
                .GroupBy(g => new { g.ApplicationForms.System, g.ApplicationForms.AccessRequiredTo, g.AccessAreaCheckBoxes.AccessAreaManagement, g.ApplicationForms.EquipmentTag })
                .Select(s => new RecordViewModel
                {
                    System = s.Key.System.Name,
                    AccessRequiredTo = s.Key.AccessRequiredTo.Name,
                    AccessArea = s.Key.AccessAreaManagement.Name,
                    EquipmentTag = s.Key.EquipmentTag.Name,
                    Count = s.Count()
                }).ToList();

除了不显示带有空值的行之外，一切都运行得很好。我是不是在LINQ中遗漏了什么？任何帮助都将不胜感激！

Answer 1

这就是我最后所做的，在这里发表供你参考。

    var active = (from ap in _context.ApplicationForms
                  join cb in _context.AccessAreaCheckBoxes
                  on ap.RecordId equals cb.RecordId into j1
                  from j2 in j1.DefaultIfEmpty()
                  where ap.EndDate == null
                  && (j2.AccessAreaManagement == null || j2.Checked == true)
                  group new { ap.System, ap.AccessRequiredTo, j2.AccessAreaManagement, ap.EquipmentTag } 
                  by new { System = ap.System.Name, Building = ap.AccessRequiredTo.Name, AccessArea = j2.AccessAreaManagement.Name, Equipment = ap.EquipmentTag.Name } into grp
                  select new RecordViewModel
                  {
                      System = grp.Key.System,
                      AccessRequiredTo = grp.Key.Building,
                      AccessArea = grp.Key.AccessArea,
                      EquipmentTag = grp.Key.Equipment,
                      Count = grp.Count()
                  }).ToList();