LINQ Left Outer Join w/ Date Range restriction on Right table?

I have two tables, a LP_task table and an lp_Update table. I want a complete list of tasks and only updates that were posted during a specific date range. LINQ doesn't seem to support any other join criteria but 'equals'or each task. I want all tasks (left table) even if they don't 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 works great for grabbing ALL LP_tasks and their respective LP_updates. If no updates available it still returns the task (left outer join)

Now i want to 开发者_如何学Pythonrestrict the updates to those in a certain date range. I can't see how to do this without taking all the tasks out of the left side that did include updates but fail to meet the date requirement. Any WHERE clause i add after upds.DefaultIfEmpty does that. Not sure what to do from here.

You can have a nested query in the join. So you can join on a filtered table then. Here's 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 VB equivalent:

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