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