实体框架导航属性上的 .Skip().Take() 正在我的 SQL Server 上执行 SELECT * [英] .Skip().Take() on Entity Framework Navigation Properties is executing SELECT * on my SQL Server

问题描述

我在生成的部分类上有一个方法，如下所示:

I have a method on my generated partial class like this:

var pChildren = this.Children
    .Skip(skipRelated)
    .Take(takeRelated)
    .ToList();

当我查看我的 SQL Server 时，我可以看到生成的代码正在执行 SELECT *.* FROM Children 此代码直接取自我的班级，我已经验证了我的顺序Skip/Take 在我的 .ToList 之前.

When I look at my SQL Server, I can see the generated code is doing a SELECT *.* FROM Children This code is taken directly from my class, I have verified that the order of my Skip/Take is BEFORE my .ToList.

如果我删除 .ToList，该行很快(并且没有 SQL 发送到我的数据库)，但是当我尝试对结果进行 foreach 时，我会收到相同的 SQL 发送到我的数据库:SELECT *.* FROM Children.

If I remove the .ToList, that line is fast (and no SQL is sent to my DB), but the moment I try to foreach over the results, I get the same SQL sent to my DB: SELECT *.* FROM Children.

在我的实体的导航属性上使用 .Skip 和 .Take 时有什么特别需要做的吗?

Is there something special I need to do when using .Skip and .Take on the navigation properties of my entities?

更新

我将尝试生成实际的 SQL，我目前还没有为此设置.我找到了第一个，因为它出现在 SSMS 的最近昂贵的查询"列表中.

I'll try to get the actual SQL generated, I'm not currently setup for that. I found the first one because it shows up in SSMS's "recenty expensive queries" list.

运行这个:

var pChildren = this.Children
    //.Skip(skipRelated)
    //.Take(takeRelated)
    .ToList();

返回约 4,000,000 行，耗时约 25 秒.

returns ~4,000,000 rows and takes ~25 seconds.

运行这个:

var pChildren = this.Children
    //.Skip(skipRelated)
    .Take(takeRelated)
    .ToList();

返回约 4,000,000 行，耗时约 25 秒.

returns ~4,000,000 rows and takes ~25 seconds.

正如我所说，我将获取为这些生成的 SQL 并将它们也摆出来.

As I said, I'll grab the SQL generated for these and pose them up as well.

推荐答案

问题是当您查询这样的子集合时，您正在执行 LINQ-to-Object 查询.EF 将加载整个集合并在内存中执行查询.

The problem is that you are performing a LINQ-to-Object query when you query a child collection like that. EF will load the whole collection and perform the query in memory.

如果你使用的是 EF 4，你可以这样查询

If you are using EF 4 you can query like this

var pChildren = this.Children.CreateSourceQuery()
                 .OrderBy(/* */).Skip(skipRelated).Take(takeRelated);

在 EF 4.1 中

var pChildren = context.Entry(this)
                   .Collection(e => e.Children)
                   .Query()
                   .OrderBy(/* */).Skip(skipRelated).Take(takeRelated)
                   .Load();

这篇关于实体框架导航属性上的 .Skip().Take() 正在我的 SQL Server 上执行 SELECT *的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！