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"
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();
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow