EF Core 3, optimize lots of Include/ThenInclude

user_7002012 0
Viewed 3090

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.

entity-framework-core sql-server c#
4 Answers

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!

user_103740420

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.

user_2854650

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.

user_14279740

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.

user_104704430
Related