EF Core 3, optimize lots of Include/ThenInclude

Question

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.

Answer 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!

Answer 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.