. 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

On my produced partial class, I have a function that looks like this:

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

My SQL Server shows that the created code is doing aSELECT *.* FROM Children This code was directly copied from my class, and I have confirmed that my Skip/Take order comes before my.ToList.

The line runs quickly if I delete the.ToList (and no SQL is sent to my DB), however the instant I attempt toforeach I got the same SQL query delivered to my database over the outcomes:SELECT *.* FROM Children .

Is there anything unique I need do before utilizing. Jump to and. Take one of my entities, please?

update

Since I'm not presently set up for it, I'll attempt to get the real SQL created. The first one was discovered by me since it is shown under "recently costly queries" in SSMS.

Executing this:

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

4,000,000 rows are returned in 25 seconds.

Executing this:

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

4,000,000 rows are returned in 25 seconds.

As I said, I'll also put up the SQL that was created for them.

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

Accepted Answer

The issue with doing so is that you are doing a LINQ-to-Object query on a child collection. The whole collection will be loaded by EF, and the query will be run in memory.

You can query in this way if you're running EF 4.

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 making a call help?Skip on the outcome ofTake ? i.e.

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

Further, 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