I have a query where I want a left join on 3 tables then groupby and perform some logic. However, the query is super slow. I think the FirstOrDefault()
could be the culprit here but cant work out why it is so slow. Can anybody spot where I may be going wrong here, and suggest ways to improve the performance of the query :)
var result =
from admins in _db.ContactAdmins
where admins.SupervisorId == assignmentNumber
from contact in _db.ContactShifts.Where(x => admins.AssignmentNumber == x.AssignmentNumber).DefaultIfEmpty()
from esr in _db.VEsrRecords.Where(x => admins.AssignmentNumber == x.AssignmentNumber).DefaultIfEmpty()
group new
{
esr,
contact
}
by new
{
AssignmentNumber = esr.AssignmentNumber,
Forename = esr.FirstName,
Surname = esr.LastName,
Email = esr.EmailAddress,
Role = esr.PositionName,
}
into dashboard
select new
{
Id = dashboard.Where(x => x.contact.IsActive).Select(x => x.contact.Id).FirstOrDefault(),
AssignmentNumber = dashboard.Key.AssignmentNumber,
Forename = dashboard.Key.Forename,
Surname = dashboard.Key.Surname,
Email = dashboard.Key.Email,
Role = dashboard.Key.Role,
ContactCount = dashboard.Where(x => x.contact.ContactShiftStart.Value.Year == DateTime.Now.Year).Sum(x => x.contact.ContactCount),
IsComplete = dashboard.OrderByDescending(x => x.contact.Id).Where(x => x.contact.IsComplete == true).Select(x => x.contact.IsComplete).FirstOrDefault(),
IsScheduled = dashboard.Where(x => x.contact.IsScheduled == true).Select(x => x.contact.IsScheduled).FirstOrDefault(),
IsStarted = dashboard.Where(x => x.contact.IsStarted == true).Select(x => x.contact.IsStarted).FirstOrDefault(),
IsActive = dashboard.Where(x => x.contact.IsActive == true).Select(x => x.contact.IsActive).FirstOrDefault(),
SentForReview = dashboard.Where(x => x.contact.IsActive == true).Select(x => x.contact.HasLeadSentForReview).FirstOrDefault(),
ClinicianSignOff = dashboard.Where(x => x.contact.IsActive == true).Select(x => x.contact.HasClinicianSignedOff).FirstOrDefault(),
LatestContact = dashboard.OrderByDescending(x => x.contact.Id).Where(x => x.contact.IsComplete == true).Select(x => x.contact.ContactShiftStart).FirstOrDefault().ToString(),
ScheduledContact = dashboard.Where(x => x.contact.IsActive).Select(x => x.contact.ContactShiftStart).FirstOrDefault().ToString(),
Guid = dashboard.Where(x => x.contact.IsActive == true).Select(x => x.contact.GuidId).FirstOrDefault()
};
return new JsonResult(result);
This query should be written without grouping.
GroupBy
has only one effective usage - calculate aggregates. In your cse, you are trying to retrieve details from the group. If you are using EF Core 7 or higher, you can use GroupJoin.This is not final and best variant, but you should learn from this query haow to retrieve data effectively. Especially look at
activeContact
, probably you can reus it for other columns calculation. Also note that insteadx.ContactShiftStart.Value.Year
filtering i have used date rabge filter, it is more effective if you have indexe onContactShiftStart
column.