.Skip（）。以（）在实体框架导航属性正在执行SELECT *在我的SQL Server [英] .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中，我可以看到生成的code是做了 SELECT *。*儿童这code是直接从我的课，我核实，我跳过/采取的顺序是在我.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儿童

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和。取的的时候做一些特别的的我的实体？

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

更新

我会尽力得到实际的SQL生成的，我目前没有设置为。我找到了第一个，因为它显示了在SSMS的recenty昂贵的查询列表中。

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

返回〜400万行，大约需要25秒。

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

运行这样的：

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

返回〜400万行，大约需要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到对象查询。 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（）。以（）在实体框架导航属性正在执行SELECT *在我的SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！