Entity Framework: How to optimize this below linq query?

.net c# entity-framework-6 linq performance

Question

I need some suggestions that how to improve this below query.

from o in this.DbContext.Set<School>().AsNoTracking()
from s in o.Teachers.DefaultIfEmpty()
where SchoolCodes.Contains(o.Code)
select new TabularItem
{
    SchoolId = o.Id,
    SchoolCode = o.Code,
    SchoolPurchaseOrderReference = o.PurchaseOrderReference,
    SchoolDescription = o.OrderDescription,
    SchoolActivityStatus = o.ActivityStatusesInternal.FirstOrDefault(os => os.ActivityName == orderLoggingActivity),
    Type = o.TypesAsString,
    CustomerCode = o.CustomerCode,
    TeacherId = s == null ? (Guid?)null : s.Id,
    TeacherCode = s == null ? null : s.Code,
    TeacherCustomerReference = s == null ? null : s.CustomerReference,
    TeacherIsImported = s == null ? (bool?)null : s.IsImported,
    TeacherIsRegisteredUnderModification = s == null ? (bool?)null : s.IsRegisteredUnderModification,
    TeacherStatus = s == null ? null : s.StatusAsString,
    TeacherStatusChangeDate = s == null ? (DateTimeOffset?)null : s.StatusChangeDate,
    IsReportInProgress = s == null ? false : s.IsReportInProgress,
    TeacherActivityStatus = s == null ? null : s.ActivityStatusesInternal.FirstOrDefault(ss => ss.ActivityName == orderLoggingActivity),
    TeacherHasUnresolvedIssue = s.TeacherIssuesInternal.Any(si => unresolvedIssueStatuses.Contains(si.StatusAsString)),
    TeacherHasAdvancePaymentInProgressInvoiceableItem = s.FractionsInternal.SelectMany(x => x.TestPRepetitionsInternal).Any(x => x.InvoiceableItem.IsAdvancePaymentInProgress),
    TeacherHasInvoicingInProgressInvoiceableItem = s.FractionsInternal.SelectMany(x => x.TestPRepetitionsInternal).Any(x => x.InvoiceableItem.IsInvoicingInProgress && x.InvoiceableItem.InvoicingStatusAsString != doNotInvoiceStatus),
    HasSchoolBasedInvoiceableItems = s.School.InvoiceableItemsInternal.Any(item => item.InvoicingStatusAsString != orderBasedInvoiceableItemStatus),
    SchoolHasInvoicingInProgressInvoiceableItem = s.School.InvoiceableItemsInternal.Any(x => x.IsInvoicingInProgress && x.InvoicingStatusAsString != doNotInvoiceStatus)
};

Here School--> Teacher --> Fraction --> TestPRepetition --> InvoiceableItem relation between tables.

please suggest me where i can improve performance. This will hit only once, so i cant use compiled query. there is no use.

1
0
2/7/2020 4:01:14 PM

Popular Answer

Simple. DO not load all the data.

Teacher -> Fraction -> TestRPepetition multiplies the amount odf data you pull.

Ef is meant to pull the data you need, now load a lot of related data into memory IN CASE YOU MAY NEED IT ONE DAY.

Pull the minimum amount of data you need in this moment, go back to the database when you need more. Optimize from there when you run into problems by adding preloads, but always keep to the minimum you need.

Right now you load all data related to all tachers within a specific code. This is likely a ridiculous amount of data that mostly is noise and not properly used in further processing.

1
2/7/2020 3:27:51 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow