EF Core 3多Include/ThenInclude查询性能优化方案咨询

Hey there! Let's tackle this slow EF Core query issue you're facing. Multiple nested Include calls often lead to performance problems due to cartesian product duplication—your database returns way more rows than necessary, and EF has to clean up the duplicates client-side. Here are practical, actionable ways to optimize this:

1. Replace Include with Select Projection (Most Efficient) #

Instead of loading entire entities with Include, use Select to fetch only the fields you actually need. This cuts down on data transfer, avoids cartesian products, and forces EF Core to generate leaner SQL.

Here's how to adapt your query with projection:

return await _ctx.Activities
    .Where(a => a.Id == id)
    .Select(a => new Activity
    {
        // Include only the Activity fields you need (don't load the whole entity!)
        Id = a.Id,
        Name = a.Name, // Add other required scalar properties here
        Description = a.Description,

        // Map collection navigation properties
        Attributes = a.Attributes.ToList(),
        Roles = a.Roles.ToList(),
        Bookmarks = a.Bookmarks.ToList(),

        // Nested collections with their own projections
        VideoMetas = a.VideoMetas.Select(vm => new VideoMeta
        {
            Id = vm.Id, // Add necessary VideoMeta fields
            Instances = vm.Instances.ToList()
        }).ToList(),

        ImageMetas = a.ImageMetas.Select(im => new ImageMeta
        {
            Id = im.Id, // Add necessary ImageMeta fields
            Instances = im.Instances.ToList()
        }).ToList(),

        // Nested reference navigation property
        Procedure = new Procedure
        {
            Id = a.Procedure.Id, // Add necessary Procedure fields
            Attributes = a.Procedure.Attributes.ToList()
        }
    })
    .FirstOrDefaultAsync();

Why this works: #

• EF Core generates targeted SQL that only queries the columns you specify.
• No duplicate rows from cartesian products = less data to transfer and process.
• You avoid loading unused properties, which saves memory and processing time.

2. Use AsSplitQuery() (Minimal Code Change) #

If you need the full entity (not just a projection) and don't want to rewrite the query, EF Core 5+ supports AsSplitQuery() to split your single large query into multiple smaller ones. This eliminates cartesian product duplication entirely.

return await _ctx.Activities
    .Include(a => a.Attributes)
    .Include(a => a.Roles)
    .Include(a => a.Bookmarks)
    .Include(a => a.VideoMetas)
        .ThenInclude(vm => vm.Instances)
    .Include(a => a.ImageMetas)
        .ThenInclude(im => im.Instances)
    .Include(a => a.Procedure)
        .ThenInclude(p => p.Attributes)
    .AsSplitQuery() // Split into multiple efficient queries
    .FirstOrDefaultAsync(a => a.Id == id);

EF Core will generate separate SQL queries for each collection navigation property, then assemble the full entity in memory. This is often much faster than a single massive query.

3. Optimize with EF Plus (Workaround for Nested Includes) #

While EF Plus's IncludeOptimized() doesn't have a direct ThenInclude() method, you can use nested Select calls inside IncludeOptimized() to handle child collections:

return await _ctx.Activities
    .IncludeOptimized(a => a.Attributes)
    .IncludeOptimized(a => a.Roles)
    .IncludeOptimized(a => a.Bookmarks)
    .IncludeOptimized(a => a.VideoMetas)
    .IncludeOptimized(a => a.VideoMetas.Select(vm => vm.Instances))
    .IncludeOptimized(a => a.ImageMetas)
    .IncludeOptimized(a => a.ImageMetas.Select(im => im.Instances))
    .IncludeOptimized(a => a.Procedure)
    .IncludeOptimized(a => a.Procedure.Select(p => p.Attributes))
    .FirstOrDefaultAsync(a => a.Id == id);

EF Plus will optimize these includes to avoid cartesian products, similar to how EF6's Include(v => v.Collection.Select(child => child.ChildCollection)) works.

4. Additional Quick Wins #

• Add Database Indexes: Ensure all foreign key fields (like VideoMeta.ActivityId, Instance.VideoMetaId, etc.) have indexes. Missing indexes are a common hidden cause of slow joins.
• Use AsNoTracking(): If you're only reading the data (not updating/saving it later), add AsNoTracking() to skip EF Core's change tracking overhead:

  _ctx.Activities.AsNoTracking()...
  

• Check Generated SQL: Use SQL Profiler or EF Core's logging to inspect the generated SQL. Look for unnecessary joins, duplicated rows, or missing indexes.

内容的提问来源于stack exchange，提问作者Expressingx