Entity Framework: How to optimize this below linq query?

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.

TomTom · Accepted Answer · 2020-02-07 15:27:51Z

1

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.

answered Feb 7, 2020 at 15:27

TomTom

62.4k11 gold badges95 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

viswa teja nudurupati Over a year ago

Since i have done select and loading only needed columns how all data of teachers loaded? Can you give any example how to load only specific columns in teachers?

TomTom Over a year ago

Yes, do not load them. SImple. You load them in the newTabularItem - throw out what you do not need.

Martin K · Accepted Answer · 2020-02-07 15:38:29Z

0

There are a couple options, but few are in EF

Clean up the null values in the database
Build the implied entity relations like TeacherHasInvoicingInProgressInvoiceableItem into the database (via foreign key or mapping table)
Prefetch repeat statements like o.ActivityStatusesInternal.FirstOrDefault

answered Feb 7, 2020 at 15:38

Martin K

19310 bronze badges

Collectives™ on Stack Overflow

Entity Framework: How to optimize this below linq query?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related