DefaultIfEmpty() does not handle empty collections

Question

I've been trying to left join the table and they are in a one-to-many relationship.

I have written a SQL query and trying to convert it into LINQ for my ASP.NET Core application.

My sql query is as follows:

    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

And my LINQ is as follows:

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();

Everything is working well except it doesn't show the rows with the NULL value. Did I miss out something in my LINQ? Any help would be greatly appreciated!

Answer 1

This is what I do in the end, post here for your reference.

    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();