Using LINQ Expressions to Multiple Left Join and use ISNULL Functionality

Question

I'm building a Step Tracking web app at work. I'm working with the latest EF Core. There are three tables I'm interacting with:

1. wg: WellnessGroup (WellnessGroupId, Name)
2. wgu: WellnessGroupUser (Look up table: WellnessGroupId, EmployeeId)
3. wsl: WellnessStepsLog (EmployeeId, StepCount)

What I want is to get all of the WellnessGroups and the total step amount for that group. If there are no steps attached to that group yet, I would like for the NULL value to be 0. I have this SQL statement which gives me the desired data:

SELECT wg.Name, SUM(ISNULL(wsl.StepCount, 0)) AS steps
FROM dbo.WellnessGroup AS wg
LEFT JOIN dbo.WellnessGroupUser AS wgu
ON wgu.WellnessGroupId = wg.Id
LEFT JOIN dbo.WellnessStepsLog AS wsl
ON wsl.EmployeeId = wgu.AzureAdUserId
GROUP BY wg.Name
ORDER BY steps DESC;

And I have managed to throw 2 LINQ expressions together on my controller which is giving me only the WellnessGroups that have steps associated with them and is not giving me the WellnessGroup data if there are no steps:

 var query = _dbContext.WellnessGroupUser
                    .Include(x => x.WellnessGroup)
                .Join(_dbContext.WellnessStepsLog, group => 
                group.AzureAdUserId, steps => steps.EmployeeId,
                    (group, steps) => new
                    {
                        Steps = steps.StepCount,
                        Date = steps.TrackedDate,
                        Group = group.WellnessGroup.Name

                    }).Where(x => x.Date >= yearToDate).Where(x => x.Date <= endDate);

 var stepsByGroup = query
                .GroupBy(x => x.Group)
                .Select(s => new
                {
                    Group = s.Key,
                    Date = s.Max(x => x.Date),
                    Steps = s.Sum(x => x.Steps)
                });

Answer 1

One way is to query all WellnessGroups and build the sum inside as a second subquery. Like this:

var query =
    db.WellnessGroup.Select(wg => new {
        wg.WellnessGroupId,
        sum = (int?) wg.WellnessGroupUser
                       .Sum(wgu => wgu.Employee.WellnessStepsLog.Sum(wsl => wsl.StepCount))
    });

Note, that the cast to (int?) is important. Otherwise sum is assumed to be int, which causes an InvalidOperationException if there is no sum for a row.

Another way is to build all the sums first. And then do the outer join with the WellnessGroups:

// sum up all stepcounts
var q1 =
    from wgu in db.WellnessGroupUser
    from wsl in db.WellnessStepsLog
    where wgu.EmployeeId == wsl.EmployeeId
    group wsl.StepCount by wgu.WellnessGroupId
    into g
    select new {WellnessGroupId = g.Key, Sum = g.Sum()};

// join with all WellnessGroups
var q2 =
    from wg in db.WellnessGroup
    join s in q1 on wg.WellnessGroupId equals s.WellnessGroupId into sj
    from sum in sj.DefaultIfEmpty()
    select new {wg, sum = (int?) sum.Sum};

---

EDIT:

Since the OP later asked in the comments how to group by more then one field. Here is an example which groups by WellnessGroup.WellnessGroupId and the month of WellnessStepsLog.TrackedDate. There can be more then one field in GroupBy by placing them in a new { ... }. So the first query creates a line per possible WellnessGroup / Month combination. The second query performs the outer join with WellnessGroup just as before:

var q1 =
    from wgu in db.WellnessGroupUser
    from wsl in db.WellnessStepsLog
    where wgu.EmployeeId == wsl.EmployeeId
    group wsl.StepCount by new { wgu.WellnessGroupId, wsl.TrackedDate.Month }
    into g
    select new {g.Key.WellnessGroupId, g.Key.Month, Sum = g.Sum()};

// join with all WellnessGroups
var q2 =
    from wg in db.WellnessGroup
    join s in q1 on wg.WellnessGroupId equals s.WellnessGroupId into sj
    from sum in sj.DefaultIfEmpty()
    select new {wg.WellnessGroupId, Month = (int?) sum.Month, Sum = (int?) sum.Sum};