I have following tables Each customer has multiple followups

and wanted to retrieve customerslist where Followups.attendedDate > customer.attendedDate and where [most recent for customer] Followup.statusid=2 or 7 or 8 or 9

Code I did

    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

Query gives customerlist where customers where statusID = 2|7|8|9 matches. But I want customers only with a most recent statusID=2|7|8|9.

So, in given sample data Customer Steve should be in result list as his last status is followup(2). Customer John Should not be in list as it has no followups. Customer Mark also should not be in result list as among his all status his last/latest status is NotInterested(1)

TIA

CodePudding user response：

It might be simpler to start from FollowUps and work back to Customer. This implicitly removes customers with zero FollowUps..

Dim stats = { 2,7,8,9 }

db.FollowUps.Include(Function(fu) fu.Customer) _
  .GroupBy(Function(fu) fu.CustomerId) _
  .Select(Function(g) g.OrderByDescending(Function(fu) fu.AttendedDate).First()) _ 
  .Where(Function(fu) stats.Contains(fu.StatusId) AndAlso fu.AttendedDate > fu.Customer.AttendedDate) _
  .Select(Function(fu) fu.Customer)

It might be possible to make the include implicit by moving the where for the attendeddates earlier

db.FollowUps _
  .Where(Function(fu) fu.AttendedDate > fu.Customer.AttendedDate) _
  .GroupBy(Function(fu) fu.CustomerId) _
  .Select(Function(g) g.OrderByDescending(Function(fu) fu.AttendedDate).First()) _ 
  .Where(Function(fu) stats.Contains(fu.StatusId)) _
  .Select(Function(fu) fu.Customer)

I'm fairly sure that EF can translate this pattern.. but not in a position to test atm.. drop a comment if you get a "query couldn't be translated"

CodePudding user response：

After running EF query

db.FollowUps _
  .Where(Function(fu) fu.AttendedDate > fu.Customer.AttendedDate) _
  .GroupBy(Function(fu) fu.CustomerId) _
  .Select(Function(g) g.OrderByDescending(Function(fu) fu.AttendedDate).First()) _ 
  .Where(Function(fu) stats.Contains(fu.StatusId)) _
  .Select(Function(fu) fu.Customer)

when I Run below code to check retrieved values

For Each cust As Customer In result2
    Debug.WriteLine(cust.Firstname & " " & cust.Lastname)
    For Each fw As FollowUp In cust.FollowUps
        Debug.WriteLine(fw.CallStatu.Status & vbTab & fw.AttendedDate)
    Next
Debug.WriteLine("---------------------")
Next

as a result I get

Chirag Panchal
Document Pickup 17/09/2021 12:02:03 PM
Document Pickup 17/09/2021 12:02:20 PM
Document Pickup 17/09/2021 12:04:33 PM
Document Pickup 17/09/2021 12:06:48 PM
Documents Received 17/09/2021 12:26:33 PM
Follow up 13/09/2021 5:06:28 PM
Interested 13/09/2021 5:07:02 PM
Interested 01/09/2021 11:56:38 AM
"---------------------"
Ravibhai Prajapati
Document Pickup 01/09/2021 11:56:45 AM

where dates seems to be in descending order but respective time is in ascending order for each record