How to convert a SQL LEFT JOIN with aggregation into a LINQ query

Data and Databases
1

I have a SQL statement that performs a LEFT JOIN and includes an aggregate function:

SELECT
   u.id,
   u.name,
   isnull(MAX(h.dateCol), '1900-01-01') dateColWithDefault
FROM universe u
LEFT JOIN history h 
   ON u.id=h.id 
   AND h.dateCol<GETDATE()-1
GROUP BY u.Id, u.name

I’m looking to translate this into LINQ but I’m not sure how to do it elegantly. The SQL retrieves all users along with their latest history date, ignoring the current date. If a user doesn’t have any history records, it should return ‘1900-01-01’. How can I achieve this effectively in LINQ while keeping the logic and efficiency intact?

2

Ryan_Nebula’s GroupJoin approach is definitely worth trying. Here’s how I usually handle this:

var result = universe.GroupJoin(
    history.Where(h => h.dateCol < DateTime.Now.AddDays(-1)),
    u => u.id,
    h => h.id,
    (u, historyRecords) => new {
        id = u.id,
        name = u.name,
        dateColWithDefault = historyRecords.Any() ? 
            historyRecords.Max(h => h.dateCol) : 
            new DateTime(1900, 1, 1)
    });

This skips the messy nested grouping and keeps things readable. GroupJoin handles left join behavior naturally - it creates empty collections for non-matching records, so null checking is much cleaner. I’ve found this translates well to SQL and runs efficiently if you’ve got proper indexes on the join columns and date filter.

3

nice approach! have you tried GroupJoin though? might be cleaner for left joins. how’s the performance with larger datasets? and does the Where clause translate to sql correctly?

4

for left join in linq, you can use DefaultIfEmpty() and Max() with null check. try this: from u in universe join h in history.Where(x => x.dateCol < DateTime.Now.AddDays(-1)) on u.id equals h.id into historyGroup from hg in historyGroup.DefaultIfEmpty() group hg by new {u.id, u.name} into g select new { id = g.Key.id, name = g.Key.name, dateColWithDefault = g.Max(x => x?.dateCol) ?? new DateTime(1900,1,1) }. you might need to adjust it a bit for your needs.