How to add left outer join to grouped and summed LINQ query

c#, linq

Solution

Use group join:

groupedOccurrences = (from e in db.Employees
                      join o in db.Occurrences.Where(x => 
                                  x.OccurrenceDate >= beginDate &&
                                  x.OccurrenceDate <= endDate)
                           on e.EmployeeID equals o.EmployeeID into g
                      select new OccurrenceByQuarter
                      {
                          Name = e.EmployeeName,
                          Total = g.Sum(x => (int?)x.Points) ?? 0
                      });

Result will be:

Jack  6
Jill  3
Roger 0

In order to return `0` for empty groups cast summarized property to nullable, and then apply null-coalescing operator to return default value: `g.Sum(x => (int?)x.Points) ?? 0`

Problem

I have an Employees table: ``` EmployeeID | EmployeeName --------------------------- 1 | Jack 2 | Jill 3 | Roger ``` And an Occurrences table: ``` OccurrenceID | EmployeeID | Points -------------------------------------- 1 | 1 | 5 2 | 2 | 3 3 | 1 | 1 ``` I have a working LINQ query that groups and sums the two table together: ``` groupedOccurrences = (from o in db.Occurrences.Include(o => o.Employee) where o.OccurrenceDate >= beginDate && o.OccurrenceDate <= endDate group o by o.Employee.EmployeeName into g select new OccurrenceByQuarter { Name = g.Key, Total = g.Sum(o => o.Points) }); ``` Which produces this output: ``` Jack 6 Jill 3 ``` But I want to also have employee Roger show up in the output with 0 points. I've tried adding a join to the LINQ query like this: ``` groupedOccurrences = (from e in db.Employees from o in db.Occurrences join o in db.Occurrences on e.EmployeeID equals o.EmployeeID into j1 from j2 in j1.DefaultIfEmpty() group j2 by e.EmployeeName into g select new OccurrenceByQuarter { Name = g.Key, Total = g.Sum(o => o.Points) }); ``` But I end up with the number of points being vastly inflated (as in 24 times what they're supposed to be). I've also tried to get the Total to return 0 if null by changing the declaration of Total to `public int? Total { get; set; }` in my OccurrencesByQuarter class but then when I try to change the LINQ query to include `Total = g.Sum(o => o.Points) ?? 0` I get an error that says "operator ?? cannot be applied to operands of type int and int". Any help would be much appreciated.

Original source