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!

DefaultIfEmpty() Returns the elements of the specified sequence or the specified value in a singleton collection if the sequence is empty. — styx, Jul 18 '19 at 09:04
Which version of EF are you using? Core or 6? Please edit the question and specify it there. My two cents: If using EF Core, I would be pretty confident that the query is not going to be translated into SQL and will be evaluated in memory. You can check this in the console, it should produce warnings. In this case, I would just use your SQL query and be done with it. The query is simple to understand and this linq.. not so much. You can take a look at Query Types for this: https://learn.microsoft.com/en-us/ef/core/modeling/query-types — jpgrassi, Jul 18 '19 at 09:52
@jpgrassi This is EF Core. Yes linq is pretty something, however I have found my solution and will post it soon, anyways thanks for your suggestion. — Brown, Jul 18 '19 at 10:29

score 0 · Accepted Answer · answered Jul 18 '19 at 10:34

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

DefaultIfEmpty() does not handle empty collections

1 Answers1