首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >缩短LINQ查询时间

缩短LINQ查询时间
EN

Stack Overflow用户
提问于 2018-07-02 20:46:27
回答 1查看 102关注 0票数 1

早上好。我正在尝试减少LINQ查询的时间。在代码块的执行过程中,对于大型数据集,大约需要30-40秒才能完成,这太长了。

代码语言:javascript
复制
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,我读取它应该更快,这几乎是执行时间的两倍。

代码语言:javascript
复制
    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表示的对象。

代码语言:javascript
复制
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对象。

代码语言:javascript
复制
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 }
    }

获取列表的数据库查询。

代码语言:javascript
复制
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
                        }
                    }
            ));
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2018-07-03 04:35:09

正如我所期望的那样,您可以在一个查询中更高效地完成此操作:

代码语言:javascript
复制
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上下文中,但在设计器中,由于某些原因,它们在默认情况下总是折叠的,因此人们往往会忽略它们。如果由于某种原因属性不在那里,您可以替换...

代码语言:javascript
复制
Bills = (from bill in visit.Bills

由...

代码语言:javascript
复制
Bills = (dbContext.ABills where visit.PatientVisitId == bill.PatientVisitId
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/51136594

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档