EF Core 9 Performance: Optimizing 'N+1 Query Problem' in complex One-to-Many relationships

Rizwana 0 Reputation points
2026-07-04T09:51:13.7866667+00:00

Hello everyone,

I am working on an ASP.NET Core project and I am struggling to optimize data retrieval for a large-scale database.

Scenario:

I have a 'User' entity with a one-to-many relationship with 'Orders'. I need to fetch all users along with their recent orders. When I use a standard foreach loop to access the related 'Orders' collection, I encounter the 'N+1' query performance issue.

My Questions:

  1. While .Include() works for small datasets, what is the best practice to handle this when dealing with thousands of records without exhausting memory?
  2. Is SplitQuery a reliable approach for complex nested relationships in production, or should I consider Projection (Select) with DTOs?
  3. Are there specific scenarios where 'Explicit Loading' outperforms 'Eager Loading' in terms of latency?

I would appreciate insights from the community on how to handle this in a real-world enterprise scenario

. Thank you!

Developer technologies | .NET | Entity Framework Core
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest

  1. AI answer

    2026-07-04T09:51:59.3166667+00:00
    1. Handling thousands of users + orders without exhausting memory

    For large result sets, the key is to:

    • Avoid lazy loading (it causes the N+1 problem and many round trips).
    • Use eager loading or projection so the database is hit in a controlled way.
    • Stream or batch results instead of materializing everything at once.

    From the EF Core guidance:

    • Lazy loading is discouraged for web apps because it easily causes N+1 and many extra round trips per request, which only show up as a problem under production load.
    • Eager loading via Include or via projection is preferred when related data is needed for all entities.
    • Always load only what is needed (avoid pulling entire graphs if only a subset of properties is required).

    For thousands of users and their recent orders:

    • Limit the data:
      • Filter users (e.g., only active, only in a date range).
      • Limit orders per user (e.g., top N recent orders) if possible.
      • Project only required columns into DTOs instead of full entities.
    • Use asynchronous APIs (ToListAsync, AsAsyncEnumerable) to keep the app scalable and avoid blocking threads.
    • Consider paging over users (e.g., 500–1000 at a time) rather than loading all users in one request.

    EF Core documentation explicitly recommends projecting only the needed properties and using async APIs to optimize data access and I/O in ASP.NET Core apps.

    1. SplitQuery vs projection with DTOs for complex nested relationships

    There are two distinct concerns:

    • How many round trips and how much duplicated data are generated.
    • How much data is materialized into memory and tracked.

    Relevant guidance:

    • Eager loading with Include on multiple collections in a single query can cause a “cartesian explosion”: the same parent row is duplicated for each child row, and this grows quickly with multiple one-to-many relationships.
    • EF Core provides split queries (AsSplitQuery) to avoid cartesian explosion by issuing separate SQL queries per included collection.
    • Split queries reduce duplicated data in each result set but still materialize the full object graph into memory.
    • Projection (with Select) lets the query return only the properties needed, which is often more efficient than Include for read-only scenarios.

    In practice:

    • For complex nested relationships where the full graph is needed as entities (e.g., for updates or domain logic), Include(...).AsSplitQuery() is a valid and supported approach to avoid cartesian explosion.
    • For read-only scenarios (like showing “users with recent orders” in a list/grid), projection to DTOs is typically the best practice:
      • Reduces columns and relationships loaded.
      • Works naturally with no-tracking queries.
      • Minimizes memory usage and network payload.

    ASP.NET Core best practices explicitly recommend:

    • Minimizing network round trips.
    • Not retrieving more data than necessary.
    • Using no-tracking queries for read-only data.
    • Filtering and projecting in the database.

    EF Core performance guidance also shows using projection instead of Include to avoid N+1 and to load only needed properties.

    So for production, complex, read-heavy queries:

    • Prefer projection with DTOs plus no-tracking queries.
    • Use AsSplitQuery() when Include is needed on multiple collections and cartesian explosion is a risk.
    1. When explicit loading can outperform eager loading

    Key points from the documentation:

    • Eager loading is usually best when related data is needed for every entity retrieved, because it uses a single query (or a controlled set of queries) and minimizes round trips.
    • However, eager loading can generate very complex join queries that SQL Server may not process efficiently, especially with many relationships or large row sizes.
    • In such cases, separate queries (explicit loading) can be more efficient:
      • If only a subset of entities needs their navigation properties accessed.
      • If the eager-loaded query becomes too complex and performs poorly or cannot be completed by the data source.

    Explicit loading characteristics:

    • Each Load call is a separate round trip.
    • Overuse (e.g., inside a loop) can reintroduce N+1 behavior and hurt performance.
    • It is most beneficial when:
      • The base set of entities is large, but only a small subset needs related data.
      • A single eager-loading query would be too complex or would return far more data than needed.

    So explicit loading can outperform eager loading in latency when:

    • The eager-loading query would be very complex (many joins, many-to-many, large graphs) and slow.
    • Only a small fraction of the base entities actually require their related collections.

    In typical “all users with their recent orders” scenarios where most users need their orders, eager loading (or projection) is usually better than explicit loading.

    Summary for an enterprise scenario like this:

    • Disable lazy loading for web apps to avoid N+1.
    • Use async EF Core APIs and no-tracking queries for read-only views.
    • For “users with recent orders”:
      • Prefer projection to DTOs with filtering and top-N per user where possible.
      • If full entities are needed and multiple collections are included, use AsSplitQuery() to avoid cartesian explosion.
    • Consider explicit loading only when:
      • A small subset of users needs orders.
      • Or eager loading generates overly complex, slow queries.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.