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

Question

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"

Praveen Ray

Related Questions

View more on Stack Overflow

Slava Utesinov · Accepted Answer · 1/31/2019 12:57:33 PM

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();

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

Question

Fastest Entity Framework Extensions

Popular Answer

Related Questions

Fastest Entity Framework Extensions

Performance

Expression Evaluator

Related