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

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"

Slava Utesinov · Answer 1 · 2019-01-31T12:57:33.893

2

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

edited Jan 31 '19 at 12:57

answered Jan 31 '19 at 12:51

Slava Utesinov

13,410
2
19
26

Slava: I get an error : `InvalidOperationException: No row is available` – Praveen Ray Jan 31 '19 at 20:35
@PraveenRay, show us your context and context creation code. – Slava Utesinov Feb 01 '19 at 05:21
@PraveenRay, are there any **inner** exceptions? – Slava Utesinov Feb 01 '19 at 10:34
Slava, it's coming from the driver: InvalidOperationException: No row is available Npgsql.NpgsqlDataReader.CheckRow() in NpgsqlDataReader.cs Npgsql.NpgsqlDataReader.CheckRowAndOrdinal(int ordinal) in NpgsqlDataReader.cs Npgsql.NpgsqlDefaultDataReader.GetFieldValue(int column) in NpgsqlDefaultDataReader.cs – Praveen Ray Feb 02 '19 at 04:06

score 1 · Answer 2 · answered Jan 31 '19 at 12:50

1

You can write Linq like this

from p in Patients
join pv in PatientVisits on p.PatientID equals pv.id into jointable
from z in jointable.DefaultIfEmpty()
select new
{
  p.FirstName, 
  p.LastName,
  pv.VisitDate,

};

answered Jan 31 '19 at 12:50

karthickj25

1,207
9
16

score 0 · Answer 3 · answered Jan 31 '19 at 13:07

My proposal would be :

public class Patient
{
    public int PatientId { get; set; }
    public string Name { get; set; }
}

public class PatientVisit
{
    public Patient Patient { get; set; }
    public DateTime VisitDate { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        Patient p1 = new Patient();
        p1.PatientId = 1;
        p1.Name = "Harry";

        Patient p2 = new Patient();
        p2.PatientId = 2;
        p2.Name = "John";

        List<PatientVisit> visits = new List<PatientVisit>();
        visits.Add(new PatientVisit
        {
            Patient = p1,
            VisitDate = DateTime.Now.AddDays(-5)
        });

        visits.Add(new PatientVisit
        {
            Patient = p1,
            VisitDate = DateTime.Now
        });

        visits.Add(new PatientVisit
        {
            Patient = p2,
            VisitDate = DateTime.Now.AddDays(-1)
        });


        var q = (from t in visits
                 select new
                 {
                     t.Patient.Name,
                     t.Patient.PatientId,
                     t.VisitDate
                 }).OrderByDescending(t=>t.VisitDate).GroupBy(x => new { x.PatientId });

        foreach (var item in q)
        {
            Console.WriteLine(item.FirstOrDefault().Name + ", " + item.FirstOrDefault().VisitDate);
        }

    }
}

score 0 · Answer 4 · answered Jan 31 '19 at 13:30

If your class definitions have virtual ICollection, you can use them:

public class Patient
{
    public int Id { get; set; }
    ...

    // every Patient has zero or more Visits (one-to-many)
    public virtual ICollection<Visit> Visits {get; set;}
}

public class Visit
{
    public int Id {get; set;}
    public DateTime VisitDate { get; set; }
    ...

    // Every Visit is done by exactly one Patient, using foreign key
    public int PatiendId {get; set;}
    public virtual Patient Patient { get; set; }
}

Requirement: give me from every Patient his latest visit time

var result = dbContext.Patients
    .Where(patient => ...)           // only if you don't want all Patients
    .Select(patient => new
    {
        // Select from every Patient only the properties you plan to use
        Id = patient.Id,
        Name = patient.Name,
        ...
        LastVisitTime = patient.Visits
            .OrderByDescenting(visit => visit.VisitDate)
            .FirstOrDefault(),
    });

If you can't use the virtual ICollections, you'll have to do the GroupJoin yourself:

var result = dbContext.Patients.GroupJoing(dbContext.Visits,
    patient => patient.Id,             // from every Patient take the Id
    visit => visit.PatientId,          // from every Visit take the PatientId,

    (patient, visits) => new           // use every patient with all his matching Visits
    {                                  // to make a new object
         Id = patiend.Id,
         Name = patient.Name,
         ...

         LastVisit = visits.OrderByDescending(visit => visit.VisitDate)
                     .FirstOrDefault(),
    });

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

4 Answers4