Cutting down LINQ Query time

Good morning. I'm trying to cut down the time on a LINQ Query. During the execution of the block of code, against large datasets it takes about 30-40 seconds to complete, which is way too long.

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


I've tried replacing the == within the where statement with .contains which I read is supposed to be quicker, that almost doubles the execution time.

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


Here's the Object that firstVisit represents.

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


Here's BillTO object.

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


Database Query to the get the list.

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


Contains

IEnumerable<T>.Contains

string.Contains

PatientVisitId

visitsWithBills

1 Answer
1

As I expected, you can do this far more efficiently in one query:

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


Now the database does all the heavy lifting of combining the objects. Everything is shaped as you want in one go.

Note that I assume the navigation property visit.Bills to exist. These properties normally exist in LINQ-to-SQL contexts, but in the designer for some reason they're always collapsed by default, so people tend to overlook them. If for some reason the property isn't there you can replace...

visit.Bills

Bills = (from bill in visit.Bills


by...

Bills = (dbContext.ABills where visit.PatientVisitId == bill.PatientVisitId


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.