.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屋!