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

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.

Hanni

What SQL did you expect this code to generate? – Flossie

I was hoping for something like SELECT TOP 10 FROM Children WHERE ParentID = @idOfParentEntity (I forget how EF handles .Skip, but I've read it is supposed to. If I execute this query against my context directly it appears to limit the data returned to a specific "page") – Hanni

The presence of .Skip() precludes the use of TOP. Unless you expected it to sum skipRelated and takeRelated and use that as the parameter to TOP... which could be an optimization, but it may also have no impact on performance. – Flossie

It may be useful to execute this query without Skip/Take and again without Take and post the SQL generated for each. One of them may be the problem. – Flossie

How then should I page through my data? Should I use something else? I would like to call parentInstance.GetChildren(pageSize * pageNum, pageSize) and only pull a small bit of data from the database. – Hanni

Possibly, but possibly not. Since SQL Server has no syntax to skip rows but only to limit the size of the result set, you may not get any performance benefit out of such a syntax, since the application side will stop reading the results after takeRelated row(s) anyway. The real killer here is the lack of a mechanism to skip rows (Skip()), not the mechanism to limit the result set (Take()). – Flossie

@Flossie - You can use ROW_NUMBER in SQL Server to return rows between 10-20 for example. – Surfactant

@MartinSmith Ah, that is new in SQL Server 2005. Thanks for pointing that out. Nate, which version of SQL Server are you using? – Flossie

I'm on SQL Server 2008 R2, I was using this for reference: msdn.microsoft.com/en-us/library/bb738702.aspx – Hanni

can you show actual query executed via the following command ((ObjectQuery<Child>)this.Children.Skip(skipRelated).Take(takeRelated)).ToTraceString() – Janniejanos

@Janniejanos I'm getting an InvalidCastException: Unable to cast object of type '<TakeIterator>d__3a1' to System.Data.Objects.ObjectQuery1 Once I nail that down, I'll post the results. – Hanni

@Hanni you need to set your own generic parameter for ObjectQuery as i do not know what type is your Children collection – Janniejanos

@Janniejanos I think I was running into the issue that Eranga mentions. Its a LINQ-To-Objects query on a Navigation property. I did update the generic paramater to the correct type, that doesn't appear to be the issue. After calling .CreateSourceQuery() the correct SQL is generated. – Hanni

Answer 1

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

Traceytrachea

Exactly what I needed. I am using EF4.1 but the first query works better for me, since I don't have access to my context from this specific method. – Hanni

Answer 2

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

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

Also, see

• TOP/LIMIT Support for LINQ?

Steiermark

take before skip doesn't make sense – Janniejanos

@vittore: Sure it does. If you want records 51-100, instead of grabbing a million to the client, throwing away 50, keeping 50, and throwing away the rest, you can grab 100 to the client and throw away 50. – Steiermark

@BenVoigt While thats true, it doesn't help as much as you get to the last of the pages. – Hanni