'EF Core 3, optimize lots of Include/ThenInclude

I have a query like this

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)
            .FirstOrDefaultAsync(a => a.Id == id);

Which turns out to be very slow. In EF 6 you can do .Include(v => v.VideoMetas.Select(vm => vm.Instances) which is a bit faster (I guess, haven't looked at SQL Profiler and actual query tbh). How can I optimize that? I can also use EF Plus where it has .IncludeOptimized() but there is no version for .ThenInclude(). I heard I can use .Select instead of .Include() but really not sure how I can handle that in this query.

c++sharpsql-serverentity-framework-core


Solution 1:[1]

You'll want to split it into multiple queries, to speed up the performance. You can use explicit loading for this. It's not the prettiest solution, but it works. Hopefully an easier solution will come in EF 5.

I'm guessing a bit on which fields are collections and which are "normal" entries, but something like this:

var activity = await _ctx.Activities.FindAsync(Id);

await context.Entry(activity)
    .Collection(a => a.Attributes)
    .LoadAsync();

await context.Entry(activity)
    .Collection(a => a.Roles)
    .LoadAsync();

await context.Entry(activity)
    .Collection(a => a.Bookmarks)
    .LoadAsync();

await context.Entry(activity)
    .Collection(a => a.VideoMetas)
    .Query()
    .Include(vm => vm.Instances)
    .LoadAsync();
 
await context.Entry(activity)
    .Collection(a => a.ImageMetas)
    .Query()
    .Include(im => im.Instances)
    .LoadAsync();

await context.Entry(activity)
    .Reference(a => a.Procedure)
    .Query()
    .Include(p => p.Attributes)
    .LoadAsync();

return activity;

Update : As of EF Core 5 Microsoft have added .AsSplitQuery() which allows doing this much less verbose with eager loading!

Solution 2:[2]

You can not. This is dimensional expansion SQL fights with. Ef 2.2 had what coudl be seen as an attempt to start with this, but they did not get it working and removed it.

Your best chance is to load in multiple (possibly parallel) queries and then stitch the results together in memory. For EF there are libraries to do that - not sure they exist for EfCore. They run the queries as multiple queries.

Solution 3:[3]

This query is bound to run slow as when parsing this to SQL will create very complex queries with all columns selected from all tables. Best thing for optimizing performance when working with these queries is to break into multiple queries and using select to select only those columns that are required.

Solution 4:[4]

If you know how many times the result of your query can change during the day. Keeping the query result in cache is not the first request, but it is faster to get the result of the next requests.

Solution 5:[5]

If you gonna load all child elements with

.ThenInclude

then don't only use explicit loading of child object with normal

.Include

like this

_ctx.VideoMetas.Include(vm => vm.Instances).Load();
_ctx.ImageMetas.Include(im => im.Instances).Load();
_ctx.Procedures.Include(p => p.Attributes).Load();

and delete ThenInclude

return await _ctx.Activities
        .Include(a => a.Attributes)
        .Include(a => a.Roles)
        .Include(a => a.Bookmarks)
        .Include(a => a.VideoMetas)
        .Include(a => a.ImageMetas)
        .Include(a => a.Procedure)
        .FirstOrDefaultAsync(a => a.Id == id);

that will raise the performance drastically, ThenInclude is a bad practice, that still applies on EF Core 6

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 TomTom
Solution 3 SmartCoder
Solution 4 Muhammet Caylak
Solution 5 Mohamad Elnaqeeb

Related Questions