缩短LINQ查询时间
缩短LINQ查询时间
提问于 2018-07-02 20:46:27
早上好。我正在尝试减少LINQ查询的时间。在代码块的执行过程中，对于大型数据集，大约需要30-40秒才能完成，这太长了。
foreach (var patientVisitId in patientVisitIds)
{
var firstVisit = visitsWithBills.First(vb => vb.Visit.PatientVisitId == patientVisitId).Visit;
firstVisit.Bills = (from visitBill in visitsWithBills
where visitBill.Visit.PatientVisitId == patientVisitId
select visitBill.Bill).ToList();
visitTOs.Add(firstVisit);
}我尝试将where语句中的==替换为.contains，我读取它应该更快，这几乎是执行时间的两倍。
foreach (var patientVisitId in patientVisitIds)
{
var firstVisit = visitsWithBills.First(vb => vb.Visit.PatientVisitId == patientVisitId).Visit;
firstVisit.Bills = (from visitBill in visitsWithBills
where visitBill.Visit.PatientVisitId.Contains(patientVisitId)
select visitBill.Bill).ToList();
visitTOs.Add(firstVisit);
}下面是firstVisit表示的对象。
public class VisitTO
{
#region { Instance properties }
/// <summary>
/// Gets or sets the bed/room number for the visit
/// </summary>
public string Bed { get; set; }
/// <summary>
/// Gets or sets the bills for the visit
/// </summary>
public List<BillTO> Bills { get; set; }
/// <summary>
/// Gets or sets the date of admission for the visit
/// </summary>
public DateTime DateOfAdmission { get; set; }
/// <summary>
/// Gets or sets the primary diagnosis for the patient
/// </summary>
public string DX1 { get; set; }
/// <summary>
/// Gets or sets the secondary diagnosis for the patient
/// </summary>
public string DX2 { get; set; }
/// <summary>
/// Gets or sets the tertiary diagnosis for the patient
/// </summary>
public string DX3 { get; set; }
/// <summary>
/// Gets or sets the quaternary diagnosis for the patient
/// </summary>
public string DX4 { get; set; }
/// <summary>
/// Gets or sets the quinary diagnosis for the patient
/// </summary>
public string DX5 { get; set; }
/// <summary>
/// Gets or sets the senary diagnosis for the patient
/// </summary>
public string DX6 { get; set; }
/// <summary>
/// Gets or sets whether the patient has been discharged
/// </summary>
public bool IsDischarged { get; set; }
/// <summary>
/// Gets or sets the patient's full name
/// </summary>
public string PatientName { get; set; }
/// <summary>
/// Gets or sets the patient's current visit ID
/// </summary>
public string PatientVisitId { get; set; }
/// <summary>
/// Gets or sets the patient's current visit ID
/// </summary>
public string PatientId { get; set; }
/// <summary>
/// Gets or sets the name of the patient's primary care physician
/// </summary>
public string PrimaryCarePhysician { get; set; }
/// <summary>
/// Gets or sets the hosting site
/// </summary>
public string Site { get; set; }
/// <summary>
/// Gets or sets the team assignment
/// </summary>
public string Team { get; set; }
#endregion { Instance properties }
}这是BillTO对象。
public class BillTO
{
#region { Public instance properties }
/// <summary>
/// Gets or sets the bill's date
/// </summary>
public DateTime Date { get; set; }
/// <summary>
/// Gets or sets the name for the doctor on the bill
/// </summary>
public string DoctorName { get; set; }
/// <summary>
/// Gets or sets the bill's type
/// </summary>
public string Type { get; set; }
/// <summary>
/// Gets or sets the encounter for this bill
/// </summary>
public string Encounter { get; set; }
/// <summary>
/// Gets or sets the CPT Code
/// </summary>
public string CptCode { get; set; }
#endregion { Public instance properties }
}获取列表的数据库查询。
private static readonly Func<MDataContext, IQueryable<VisitBillTO>> ActiveVisitsWithBillsQuery =
CompiledQuery.Compile<MContext, IQueryable<VisitBillTO>>(
dbContext => (
from visit in dbContext.AV
join bill in dbContext.ABills on visit.PatientVisitId equals bill.PatientVisitId
where (visit.BFlag == null || visit.BFlag != "BI")
orderby visit.PatientVisitId
select new VisitBillTO
{
Bill = new BillTO
{
Date = bill.Date.GetValueOrDefault(DateTime.Today),
DoctorName = bill.DoctorName,
Type = bill.Type,
Encounter = bill.Encounter,
CptCode = bill.CptCode
},
Visit = new VisitTO
{
Bed = visit.Bed,
DateOfAdmission = visit.DateOfAdmission.GetValueOrDefault(DateTime.Today),
DX1 = visit.DX1,
DX2 = visit.DX2,
DX3 = visit.DX3,
DX4 = visit.DX4,
DX5 = visit.DX5,
DX6 = visit.DX6,
IsDischarged = (visit.IsDischargedCode != null && visit.IsDischargedCode == "Y"),
PatientName = (visit.PatientFullName ?? visit.PatientLastName + ", " + visit.PatientFirstName),
PatientVisitId = visit.PatientVisitId,
PatientId = visit.PatientID,
PrimaryCarePhysician = visit.PrimaryCarePhysician,
Site = visit.Site,
Team = visit.Team
}
}
));回答 1
Stack Overflow用户
回答已采纳
发布于 2018-07-03 04:35:09
正如我所期望的那样，您可以在一个查询中更高效地完成此操作：
from visit in dbContext.AV
where (visit.BFlag == null || visit.BFlag != "BI")
&& patientVisitIds.Contains(visit.PatientVisitId)
orderby visit.PatientVisitId
select new VisitBillTO
{
Bed = visit.Bed,
...
Team = visit.Team,
Bills = (from bill
in visit.Bills
select new BillTO
{
Date = bill.Date.GetValueOrDefault(DateTime.Today),
DoctorName = bill.DoctorName,
Type = bill.Type,
Encounter = bill.Encounter,
CptCode = bill.CptCode
})
}现在，数据库完成了组合对象的所有繁重工作。一切都按照你想要的一气呵成。
请注意，我假设导航属性visit.Bills已经存在。这些属性通常存在于LINQ- to -SQL上下文中，但在设计器中，由于某些原因，它们在默认情况下总是折叠的，因此人们往往会忽略它们。如果由于某种原因属性不在那里，您可以替换...
Bills = (from bill in visit.Bills由...
Bills = (dbContext.ABills where visit.PatientVisitId == bill.PatientVisitId页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51136594
复制相关文章
相似问题
腾讯云开发者
