EF Core 3，优化大量Include/ThenInclude [英] EF Core 3, optimize lots of Include/ThenInclude

问题描述

我有一个这样的查询

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);

结果证明这很慢.在 EF 6 中，您可以执行 .Include(v => v.VideoMetas.Select(vm => vm.Instances) 这会更快一些(我猜，没有看过 SQL Profiler 和实际查询 tbh.我该如何优化它?我也可以使用 EF Plus 在它有 .IncludeOptimized() 但没有.ThenInclude() 的版本.我听说我可以使用 .Select 而不是 .Include() 但真的不知道如何处理在此查询中.

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.

推荐答案

您需要将其拆分为多个查询，以加快性能.您可以为此使用显式加载.这不是最漂亮的解决方案，但它有效.希望 EF 5 中有更简单的解决方案.

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;

这篇关于EF Core 3，优化大量Include/ThenInclude的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！