我有兩張桌子：患者和患者訪問。一個病人可以有很多次就診。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"
4 回答
慕婉清6462132
TA貢獻1804條經驗 獲得超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();
斯蒂芬大帝
TA貢獻1827條經驗 獲得超8個贊
你可以這樣寫 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,
};
波斯汪
TA貢獻1811條經驗 獲得超4個贊
我的建議是：
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);
}
}
}
揚帆大魚
TA貢獻1799條經驗 獲得超9個贊
如果您的類定義具有虛擬 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(),
});
- 4 回答
- 0 關注
- 122 瀏覽
添加回答
舉報
0/150
提交
取消