Join two tables with one to many relationship and pick latest from multiple side

c# entity-framework-core linq

Question

I have two tables: Patients and PatientVisits . A Patient can have many visits. There are corresponding Model classes in C#.

How would I write a Linq query to get each Patient and it's latest visit date?

The raw SQL which does this is:

select max(p."FirstName"), max(p."LastName"), max(pv."VisitDate")
from "Patients" p 
left outer join "PatientVisits" pv ON pv."PatientID" = p."ID" 
group by p."ID"
1
1
1/31/2019 11:55:46 AM

Popular Answer

var answer = (from p in context.Patients
              join v in context.PatientVisits on p.ID equals v.PatientID into subs
              from sub in subs.DefaultIfEmpty()
              group sub by new { p.ID, p.FirstName, p.LastName } into gr
              select new 
              {
                  gr.Key.FirstName,
                  gr.Key.LastName,
                  VisitDate = gr.Max(x => x == null ? null : (DateTime?)x.VisitDate)
              }).ToList();
2
1/31/2019 12:57:33 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow