EF Core 3，优化很多包含/然后包含 [英] 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（）的版本。我听说我可以使用。选择而不是 .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，优化很多包含/然后包含的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！