Entity Frame Core Taking too long load data using ThenInclude-CodePudding

I am working on application where I need to load navigation properties. I have parent child relation entities and also have sub child information which I need to load but using ThenInclude taking too long to load information. Here is my implementation

var _result = GetAll()
                .Include(x => x.UserEmails)
                .Include(x => x.UserSocialMediaLinks)
                .Include(x => x.UserPhoneNumbers)
                .Include(x => x.UserTrades)
                .Include(x => x.Tags)
                .Include(x => x.Address)
                .Include(x => x.EmployeeAvailabilities)
                .ThenInclude(x => x.EmployeeAvailabilityBreaks)
                .Include(x => x.UserPayrollItems)
                .ThenInclude(x => x.PayrollItemEntity)
                .Include(x => x.UserPayrollItems)
                .ThenInclude(x => x.TimeLogEntities)
                .Where(x => x.IsDeleted != true && x.UserStatusType == UserStatusTypes.Active).ToListAsync();

I have also implement it using DTOs but still facing loading issue.

CodePudding user response：

Try using Split Queries instead of the normal strategy of generating one large query for all your Includes.

CodePudding user response：

Chances are you are facing a situation where you are simply trying to load far too much data at one time. For instance if A user can be associated to many Payroll Items, and each of these has a potentially very large number of Time Logs would be my immediate suspect looking at that load attempt. Rather than "how can I load all of this data faster", the first question is "what data do I really need?" If you are loading the data to present to a view then project it down to the DTOs/ViewModels loading only the data that the consuming view/recipient actually needs. Related data can always be fetched asynchronously and paginated if the consumer might need to expand out to see something "big" like Time Logs. I.e. If the user selects a User Row and wants to view the Time Logs, they send a request to the server to fetch the time logs for that row, and then expand a region and load the results, rather than expecting to pre-load all Logs for all user records returned.

Another warning sign I see is that your operation is not being awaited. I would assume from the code structure that GetAll() is returning an IQueryable<User>, but the ToListAsync() method should be awaited to get the result. This also hints at using a Generic repository pattern, which is an anti-pattern for EF. A repository serving this area can have a GetUsers() method to return summary details about all active users, then consider methods like GetTimeLogsForUser(userId) to handle asynchronous calls from the client to fetch expanded data if and when requested.

Situations where you typically do want to load related data are for things like Updates, but these are generally loading a complete entity structure for a single top-level row, not several top-level rows all at once. Even then it helps to make the update operations more atomic, editing and updating related data separately wherever possible.