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

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

1. 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?

2. 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).

3. 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?

Answer 1

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.

Answer 2

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.

Answer 3

The other thing to be aware of is that IQueryable makes no guarantees on either (a) the semantic reasoning of the underlying provider, or (b) how much of the set of IQueryable methods are implemented by the provider.

For example: -

1. EF does not support Last().
2. Nor does it support time-part comparisons of DateTimes into valid T-SQL.
3. It doesn't support FirstOrDefault() in subqueries.

In such circumstances you need to bring data back to the client and then perform further evaluation client-side.

You also need to have an understanding of "how" it parses the LINQ pipeline to generate (in the case of EF) T-SQL. So you sometimes have to think carefully about how you construct your LINQ queries in order to generate effective T-SQL.

Having said all that, IQueryable<> is an extremely powerful tool in the .NET framework and well worth getting more familiar with.