. Skip(). SELECT * on my SQL Server is being executed by Take() on Entity Framework Navigation Properties.

.net c# entity-framework navigation-properties sql-server-2008-r2

Question

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

var pChildren = this.Children
    .Skip(skipRelated)
    .Take(takeRelated)
    .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.

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.

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

update

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.

Running this:

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

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

Running this:

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

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

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

1
8
12/28/2011 11:32:24 PM

Accepted Answer

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.

If you are using EF 4 you can query like this

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

In EF 4.1

var pChildren = context.Entry(this)
                   .Collection(e => e.Children)
                   .Query()
                   .OrderBy(/* */).Skip(skipRelated).Take(takeRelated)
                   .Load();
7
12/29/2011 12:04:12 AM

Popular Answer

Does it help if you call Skip on the result of Take? i.e.

table.Take(takeCount+skipCount).Skip(skipCount).ToList()

Also, see



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow