联接具有一对多关系的两个表，并从多端选择最新表

Question

我有两张桌子：患者和患者访问。一个病人可以有很多次就诊。C# 中有相应的模型类。我如何编写Linq查询以获取每位患者及其最新就诊日期？执行此操作的原始 SQL 是：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"

Answer 1

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

Answer 2

你可以这样写 Linq

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,

};

Answer 3

我的建议是：

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

        }

    }

}

Answer 4

如果您的类定义具有虚拟 ICollection，则可以使用它们：

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; }

}

要求：给我每个病人他的最新就诊时间

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

    });

如果您无法使用虚拟 ICollections，则必须自己执行 Group Join：

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

    });