基于关联表最新记录值检索目标客户列表的EF查询问题

现有如下数据表，每个客户对应多条Followups跟进记录：


需要检索满足以下条件的客户列表：

• 存在Followups.attendedDate > customer.attendedDate的跟进记录
• 客户的最新一条Followup记录的statusid为2、7、8、9中的任意一个

原有实现代码如下：

Dim Result As List(Of Customer) = Await db.Customers.OrderByDescending(Function(x) x.AttendedDate) _
.Include(Function(x) x.FollowUps.Select(Function(y) y.CallStatu)) _
.Where(Function(x) x.AttendedBy IsNot Nothing Andalso 
 x.FollowUps.Any(Function(z) z.StatusID = 2 Or z.StatusID = 7 Or z.StatusID = 8 Or z.StatusID = 9)) _
.Where(Function(x) x.FollowUps.Where(Function(y) y.AttendedDate > x.AttendedDate).Count > 0).ToListAsync
    
'''To remove status other than 2, 7, 8, 9 add entries to list named removelist                           
Dim RemoveList As New List(Of Customer)
    
'Find entries without last status value 2,7,8,9 for each customer
For Each cust As Customer In Result
    If cust.FollowUps.Last.StatusID <> 2 And cust.FollowUps.Last.StatusID <> 7 And cust.FollowUps.Last.StatusID <> 8 cust.FollowUps.Last.StatusID <> 9 Then
        RemoveList.Add(cust)
    End If
Next
        
'Remove entries from original result    
For Each Cust As Customer In RemoveList
    Result.Remove(Cust)
Next

原有代码的问题：当前查询返回的是所有存在statusID为2|7|8|9跟进记录的客户，没有过滤最新跟进的状态要求。比如示例数据中，客户Steve的最新跟进状态为followup(2)，应该在结果中；客户John没有跟进记录，不应出现在结果中；客户Mark的最新跟进状态为NotInterested(1)，也不应出现在结果中。

原有代码存在两个核心问题：

1. 查询阶段的状态判断是Any，只要有任意一条跟进符合状态就会被保留，没有限定是最新跟进
2. 后续遍历判断FollowUps.Last时，没有对跟进记录按时间排序，取到的不一定是最新的跟进记录

你可以直接在EF查询阶段完成所有条件过滤，不需要后续遍历移除不符合的条目，优化后的代码如下：

' 定义目标状态集合，后续维护更方便
Dim targetStatus = {2, 7, 8, 9}
Dim Result As List(Of Customer) = Await db.Customers.OrderByDescending(Function(x) x.AttendedDate) _
.Include(Function(x) x.FollowUps.Select(Function(y) y.CallStatu)) _
.Where(Function(x)
    ' 基础条件：跟进人不为空
    Return x.AttendedBy IsNot Nothing _
    ' 过滤无跟进记录的客户
    AndAlso x.FollowUps.Any() _
    ' 条件1：存在跟进时间晚于客户到访时间的记录
    AndAlso x.FollowUps.Any(Function(y) y.AttendedDate > x.AttendedDate) _
    ' 条件2：最新的跟进记录状态属于目标集合
    AndAlso targetStatus.Contains(x.FollowUps.OrderByDescending(Function(y) y.AttendedDate).First().StatusID)
End Function).ToListAsync

注：如果你的跟进记录有独立的自增ID，也可以按ID降序排序取第一条，效率会比按时间排序更高

内容的提问来源于stack exchange，提问作者Parag