LINQ左外连接在右表上有日期范围限制吗？

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)

我有两个表,一个LP_task表和一个lp_Update表。我想要一个完整的任务列表,只需要在特定日期范围内发布的更新。 LINQ似乎不支持任何其他连接标准,但“等于”或每项任务。我想要所有任务(左表),即使他们没有更新。 (左外连接)

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)

这非常适合抓取所有LP_tasks及其各自的LP_updates。如果没有可用的更新,它仍然返回任务(左外连接)

Now i want to restrict 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.

现在我想将更新限制在某个日期范围内。我没有看到如何做到这一点,如果没有从左侧做出包括更新但未能满足日期要求的所有任务。我在upds.DefaultIfEmpty之后添加的任何WHERE子句都会这样做。不知道该怎么做。

1 个解决方案

#1

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#:

您可以在联接中使用嵌套查询。所以你可以加入一个过滤后的表格。以下是如何在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:

和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

#1