8

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.

Nate
  • 30,286
  • 23
  • 113
  • 184
  • What SQL did you expect this code to generate? – cdhowie Dec 28 '11 at 23:19
  • 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") – Nate Dec 28 '11 at 23:20
  • 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. – cdhowie Dec 28 '11 at 23:23
  • 1
    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. – cdhowie Dec 28 '11 at 23:23
  • 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. – Nate Dec 28 '11 at 23:24
  • 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()`). – cdhowie Dec 28 '11 at 23:25
  • 1
    @cdhowie - You can use `ROW_NUMBER` in SQL Server to return rows between `10-20` for example. – Martin Smith Dec 28 '11 at 23:28
  • @MartinSmith Ah, that is new in SQL Server 2005. Thanks for pointing that out. Nate, which version of SQL Server are you using? – cdhowie Dec 28 '11 at 23:30
  • I'm on SQL Server 2008 R2, I was using this for reference: http://msdn.microsoft.com/en-us/library/bb738702.aspx – Nate Dec 28 '11 at 23:31
  • 2
    can you show actual query executed via the following command `((ObjectQuery)this.Children.Skip(skipRelated).Take(takeRelated)).ToTraceString()` – vittore Dec 28 '11 at 23:39
  • @vittore I'm getting an InvalidCastException: `Unable to cast object of type 'd__3a`1' to System.Data.Objects.ObjectQuery`1` Once I nail that down, I'll post the results. – Nate Dec 28 '11 at 23:51
  • @Nate you need to set your own generic parameter for ObjectQuery as i do not know what type is your Children collection – vittore Dec 29 '11 at 00:09
  • @vittore 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. – Nate Dec 29 '11 at 00:17

2 Answers2

7

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();
Eranga
  • 32,181
  • 5
  • 97
  • 96
  • 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. – Nate Dec 29 '11 at 00:13
1

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

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

Also, see

Ben Voigt
  • 277,958
  • 43
  • 419
  • 720
  • 5
    take before skip doesn't make sense – vittore Dec 28 '11 at 23:40
  • 1
    @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. – Ben Voigt Dec 28 '11 at 23:43
  • @BenVoigt While thats true, it doesn't help as much as you get to the last of the pages. – Nate Dec 29 '11 at 00:12