Performance implementing Entity Framework Core 6 when querying large datasets with Include
I tried several approaches but none seem to work. I'm experiencing important performance degradation when querying a large dataset using Entity Framework Core 6. The query involves multiple related entities that I am attempting to include using the `.Include()` method. For context, I have a `User` entity that has many `Orders`, and each `Order` has several `OrderItems`. When I run the following query: ```csharp var usersWithOrders = await _context.Users .Include(u => u.Orders) .ThenInclude(o => o.OrderItems) .ToListAsync(); ``` This results in a very lengthy execution time, especially as the number of `Orders` and `OrderItems` increases. I tried breaking down the query and using `.AsNoTracking()` to improve performance: ```csharp var usersWithOrders = await _context.Users .AsNoTracking() .Include(u => u.Orders) .ThenInclude(o => o.OrderItems) .ToListAsync(); ``` However, this didn't help as much as I hoped. I also checked the database indices and made sure that the foreign keys are indexed yet still no important improvement in performance. The database is SQL Server 2019, and Iām using EF Core 6. Iām wondering if there are better approaches or best practices for handling such queries efficiently in EF Core. Are there any specific techniques I can use to optimize this query or alternatives to consider? Any insights would be greatly appreciated! Any help would be greatly appreciated!