LINQ to Entities -- OrderBy().ToLisT() vs. ToList().OrderBy()

entity-framework linq linq-to-entities performance

Question

I'm looking for confirmation/clarification with these LINQ expressions:

var context = new SomeCustomDbContext()

// LINQ to Entities?
var items  = context.CustomItems.OrderBy(i => i.Property).ToList();

// LINQ to Objects?
var items2 = context.CustomItems.ToList().OrderBy(i => i.Property);

(Q1) Am I correct in thinking the first method is LINQ to Entities where EF builds a more specific SQL statement to pass on, putting the ordering effort on on the database?

(Q2) Is the second method LINQ to Objects where LINQ drags the whole collection into memory (the ToList() enumeration?) before ordering thus leaving the burden on the server side (the web server in this case)?

If this is the case, I can quickly see situations where L2E would be advantageous (ex. filtering/trimming collections before pulling them into memory).

(Q3) But are there any other details/trade-offs I should be aware of, or times when "method 2" might be advantageous over the first method?

UPDATE:

Let's say we are not using EntityFramework, this is still true so long as the underlying repository/data source implements IQueryable<T> right? And if it doesn't both these statements result in LINQ to Objects operations in memory?

1
7
12/11/2012 7:39:51 PM

Accepted Answer

  1. Yes.
  2. Yes.
  3. Yes.

You are correct that calling ToList() forces linq-to-entities to evaluate and return the results as a list. As you suspect, this can have huge performance implications.

There are cases where linq-to-entities cannot figure out how to parse what looks like a perfectly simple query (like Where(x => SomeFunction(x))). In these cases you often have no choice but to call ToList() and operate on the collection in memory.


In response to your update:

ToList() always forces everything ahead of it to evaluate immediately, as opposed to deferred execution. Take this example:

someEnumerable.Take(10).ToList();

vs

someEnumerable.ToList().Take(10);

In the second example, any deferred work on someEnumerable must be executed before taking the first 10 elements. If someEnumerable is doing something labor intensive (like reading files from the disk using Directory.EnumerateFiles()), this could have very real performance implications.

9
12/11/2012 7:49:15 PM

Popular Answer

Am I correct in thinking the first method is LINQ to Entities where EF builds a more specific SQL statement to pass on, putting the ordering effort on on the database?

Yes

Is the second method LINQ to Objects where LINQ drags the whole collection into memory ... before ordering thus leaving the burden on the server side ...?

Yes

But are there any other details/trade-offs I should be aware of, or times when "method 2" might be advantageous over the first method?

There will be many times where Method 1 is not possible - usually when you have a complex filter or sort order that can't be directly translated to SQL (or more appropriately where EF does not support a direct SQL translation). Also since you can't transmit lazy-loaded IQueryables over-the-wire, any time you have to serialize a result you're going to have to materialize it first with ToList() or something similar.



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