LINQ Left Outer Join w / Date Range Limit in Right Table?

Question

I have two tables, the LP_task table and the lp_Update table . I want a complete list of tasks and only updates that have been published over a specific date range. LINQ does not seem to support any other joining criteria, but "equal" or to every task. I want all tasks (left table), even if they do not have an update. (Left Outer Join)

Dim TasksData = (From t In db.LP_Tasks _               
            Group Join up In db.LP_Updates On t.ID Equals up.TaskID Into upds = Group _
            From u In upds.DefaultIfEmpty _
            Order By t.TaskOrder, t.TaskNote, u.DateCreated Descending _
            Select New With {t.ID, t.TaskNote, u.UpdateNote, u.DateCreated})

This is great for capturing all LP_tasks and their corresponding LP_updates. If there are no updates, it still returns the task (left outer join)

Now I want to limit updates to those that are in a specific date range. I can’t figure out how to do this without accepting all the tasks from the left side, which include updates, but do not meet the requirements of the date. Any WHERE clause that I add after upds.DefaultIfEmpty does this. Not sure what to do next.

Answer 1

The union may have a subquery. This way you can join the filtered table. Here is how you can write such a query in C #:

// broken up for readability
var tenYearsAgo = DateTime.Now.AddYears(-10);
var filtered = db.LP_Updates.Where(up => up.DateCreated > tenYearsAgo);
var query = from t in db.LP_Tasks
            join up in filtered on t.ID equals up.TaskID into upds
            from u in upds.DefaultIfEmpty()
            orderby t.TaskOrder, t.TaskNote, u.DateCreated descending
            select new { t.ID, t.TaskNote, u.UpdateNote, u.DateCreated };

And the equivalent of VB:

Dim tenYearsAgo = DateTime.Now.AddYears(-10)
Dim filtered = db.LP_Updates.Where(Function(up) up.DateCreated > tenYearsAgo)
Dim query = From t In db.LP_Tasks                                                _
            Group Join up In filtered On t.ID Equals up.TaskID Into upds = Group _
            From u In upds.DefaultIfEmpty                                        _
            Order By t.TaskOrder, t.TaskNote, u.DateCreated Descending           _
            Select t.ID, t.TaskNote, u.UpdateNote, u.DateCreated