EF Core嵌套的Linq选择结果在N + 1个SQL查询中 [英] EF Core nested Linq select results in N + 1 SQL queries

问题描述

我有一个数据模型，其中顶部"对象具有0到N个子"对象.在SQL中，这是通过外键dbo.Sub.TopId来实现的.

I have a data model where a 'Top' object has between 0 and N 'Sub' objects. In SQL this is achieved with a foreign key dbo.Sub.TopId.

var query = context.Top
    //.Include(t => t.Sub) Doesn't seem to do anything
    .Select(t => new {
        prop1 = t.C1,
        prop2 = t.Sub.Select(s => new {
            prop21 = s.C3 //C3 is a column in the table 'Sub'
        })
        //.ToArray() results in N + 1 queries
    });
var res = query.ToArray();

在Entity Framework 6中(关闭了延迟加载)，此Linq查询将转换为单个 SQL查询.结果将完全加载，因此res[0].prop2将是已经填充的IEnumerable<SomeAnonymousType>.

In Entity Framework 6 (with lazy-loading off) this Linq query would be converted to a single SQL query. The result would be fully loaded, so res[0].prop2 would be an IEnumerable<SomeAnonymousType> which is already filled.

使用EntityFrameworkCore(NuGet v1.1.0)时，子集合尚未加载，其类型为:

When using EntityFrameworkCore (NuGet v1.1.0) however the sub-collection is not yet loaded and is of type:

System.Linq.Enumerable.WhereSelectEnumerableIterator<Microsoft.EntityFrameworkCore.Storage.ValueBuffer, <>f__AnonymousType1<string>>.

直到您对其进行迭代，才会加载数据，从而导致N + 1个查询.当我将.ToArray()添加到查询中(如注释所示)时，数据已完全加载到var res中，但是使用SQL事件探查器显示这在1个SQL查询中不再实现.对于每个顶部"对象，将对子"表执行查询.

The data will not be loaded until you iterate over it, resulting in N + 1 queries. When i add .ToArray() to the query (as shown in comments) the data gets fully loaded into var res, using a SQL profiler however shows this isn't achieved in 1 SQL query anymore. For each 'Top' object a query on the 'Sub' table is executed.

首先指定.Include(t => t.Sub)似乎没有任何改变.使用匿名类型似乎也不是问题，用new MyPocoClass { ... }替换new { ... }块不会改变任何内容.

First specifying .Include(t => t.Sub) doesn't seem to change anything. The use of anonymous types doesn't seem to be the problem either, replacing the new { ... } blocks with new MyPocoClass { ... } doesn't change anything.

我的问题是:是否有一种方法可以使行为类似于EF6，即立即加载所有数据?

注意:我意识到在此示例中，可以通过在执行查询后像在中那样在内存中创建匿名对象来解决此问题:

Note: i realize that in this example the problem can be fixed by creating the anonymous objects in memory after executing the query like so:

var query2 = context.Top
    .Include(t => t.Sub)
    .ToArray()
    .Select(t => new //... select what is needed, fill anonymous types

但是，这只是一个示例，实际上我确实需要创建对象作为Linq查询的一部分，因为AutoMapper使用此对象将DTO填充到我的项目中

However this is just an example, i do actually need the creation of objects to be part of the Linq query as AutoMapper uses this to fill DTOs in my project

更新:经过新EF Core 2.0的测试，仍然存在问题. (21-08-2017)

Update: Tested with the new EF Core 2.0, issue is stil present. (21-08-2017)

在aspnet/EntityFrameworkCore GitHub存储库上跟踪了问题:问题4007

Issue is tracked on aspnet/EntityFrameworkCore GitHub repo: Issue 4007

更新:一年后，此问题已在版本2.1.0-preview1-final中修复. (2018-03-01)

Update: A year later, this issue has been fixed in version 2.1.0-preview1-final. (2018-03-01)

更新:EF 2.1版已发布，其中包含一个修复程序.请参阅下面的答案. (2018-05-31)

Update: EF version 2.1 has been released, it includes a fix. see my answer below. (2018-05-31)

推荐答案

GitHub问题#4007 已标记为里程碑2.1.0-preview1的closed-fixed.现在，可以在 NuGet .NET博客文章.

The GitHub issue #4007 has been marked as closed-fixed for milestone 2.1.0-preview1. And now the 2.1 preview1 has been made available on NuGet as discussed in this .NET Blog post.

版本2.1正确的版本也已发布，请使用以下命令进行安装:

Version 2.1 proper is also released, install it with the following command:

Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 2.1.0

然后在嵌套的.Select(x => ...)上使用.ToList()指示应立即获取结果.对于我的原始问题，它看起来像这样:

Then use .ToList() on the nested .Select(x => ...) to indicate the result should be fetched immediately. For my original question this looks like this:

var query = context.Top
    .Select(t => new {
        prop1 = t.C1,
        prop2 = t.Sub.Select(s => new {
            prop21 = s.C3
        })
        .ToList() // <-- Add this
    });
var res = query.ToArray(); // Execute the Linq query

这将导致在数据库上运行2个SQL查询(而不是N + 1)；首先是基于Key-ForeignKey关系SELECT FROM'Top'表，然后是SELECT FROM'Sub'表和INNER JOIN FROM'Top'表>.然后将这些查询的结果合并到内存中.

This results in 2 SQL queries being run on the database (instead of N + 1); First a plain SELECT FROM the 'Top' table and then a SELECT FROM the 'Sub' table with an INNER JOIN FROM the 'Top' table, based on Key-ForeignKey relation [Sub].[TopId] = [Top].[Id]. The results of these queries are then combined in memory.

结果正好符合您的期望，并且与EF6所返回的结果非常相似:匿名类型'a的数组，其属性为prop1和prop2，其中prop2是匿名类型的列表'b具有属性prop21.最重要的是所有这些都在.ToArray()调用后全部加载！

The result is exactly what you would expect and very similar to what EF6 would have returned: An array of anonymous type 'a which has properties prop1 and prop2 where prop2 is a List of anonymous type 'b which has a property prop21. Most importantly all of this is fully loaded after the .ToArray() call!

这篇关于EF Core嵌套的Linq选择结果在N + 1个SQL查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！