difference of calling OrderBy() before Where() and vice versa

Question

I am wondering... In my application, I am doing something like this:

var threads = scykDb.Threads
                    .AsQueryable()
                    .Where(condition)
                    .OrderByDescending(t => t.DateCreated)
                    .Skip(threadsToSkip)
                    .Take(threadsPerPage)
                    .Select(t => t)
                    .ToList();

What would happen, if i did OrderBy() before Where()? Does it matter and what about skip() or take(), does position of those matters too?

var threads = scykDb.Threads
                    .AsQueryable()
                    .OrderByDescending(t => t.DateCreated)
                    .Where(condition)
                    .Skip(threadsToSkip)
                    .Take(threadsPerPage)
                    .Select(t => t)
                    .ToList();

Linq has a delayed execution that will happen as late as possible so that all expressions will be evaluated in the tree at once. The order should not matter. — Mike_Matthews_II, Mar 15 '13 at 03:04
@Dan, you need .ToList() to actually `download` data from database. I don't know exactly how does it works, but until i call .ToList() `threads` is only a query, and not the data. — ojek, Mar 15 '13 at 03:04
OP, when you iterate the query (such as in a foreach loop), you will invoke it and materialize the results from the database, so ToList() is not *strictly* necessary. That said, forcing it upfront in a predictable fashion in a repository is something I choose to do. Dan's suggestion is valid when you *want* deferred execution, but sometimes you simply do not want that. — Anthony Pegram, Mar 15 '13 at 03:08

score 9 · Accepted Answer · answered Mar 15 '13 at 02:59

9

If your query is actually going against a database via Entity Framework, Linq-to-SQL, or another such provider, then the order of invocation in your example does not matter.

If this is happening against an in-memory collection, you are ordering before filtering, which would be less ideal than applying the filter first. You are ordering elements that you might just discard, for example. (Ordering involves comparatively a lot of work. Filtering first reduces that work.)

That said, I would argue for the second form even if going against the database, as the same code would be equally valid for in-memory querying, so develop the habit of writing code in the normally ideal manner. It's also arguable that you would expect to see filtering prior to ordering as a matter of thought process.

answered Mar 15 '13 at 02:59

Anthony Pegram

123,721
27
225
246

4

I note that it is certainly possible to write an implementation of Linq-to-objects that detects when the where happens after the orderby and optimizes the workflow just like a database would. The implementation does not actually do so, but it would be possible. The implementation of Linq-to-objects *does* do some minor reorganization of where and select clauses to make them more efficient in common cases. – Eric Lippert Mar 15 '13 at 03:23
Have you ever blogged about this Where/Select reorganization? I would be curious about what, why, and when it would do it, how it might impact or avoid methods (anonymous or otherwise) with side effects, etc. – Anthony Pegram Mar 15 '13 at 18:11
I have not but Jon has (with a small amount of input from me). http://msmvps.com/blogs/jon_skeet/archive/2011/06/16/linq-to-objects-and-the-performance-of-nested-quot-where-quot-calls.aspx -- the impact is to improve performance in most cases, and as Jon points out, to make it worse in some pathological cases. There should never be a semantic change caused by the rearrangement. – Eric Lippert Mar 15 '13 at 18:25
Thank you. I will dive into Jon's blog in my copious spare time. – Anthony Pegram Mar 15 '13 at 18:29

difference of calling OrderBy() before Where() and vice versa

1 Answers1