When I Run the query, SQL query will built according to the expression tree no matter how weird the order of the methods.
Why it doesn't work the same with LINQ to objects?
when I enumerate an IQueryable all the projections can be placed in a rational order(e.g. Order By after Where) just like the Data Base optimizer does.

By looking at the method calls, you can see why ordering matters. In this case, by placing OrderBy first, you're effectively nesting it into the inner-most method call. This means the entire collection will get ordered when the resutls are enumerated. If you were to switch the order:

@gdoron Expression trees wouldn't necessarily help. With IQueryable<T>, the provider can turn the expression tree into something that executes on the server and returns the appropriate results. With LINQ to Objects, everything's always going to execute in memory. The overhead of computing the expression (which is real) wouldn't likely be recovered by any increase in performance...
–
Reed CopseyDec 21 '11 at 20:20

@gdoron In some cases, it ~could~ make a difference. However, even if your case, the difference could be huge or very minimal, or non-existent, depending on the filter. If the filter returns most results, it would make little difference...
–
Reed CopseyDec 21 '11 at 20:21

With linq-to-objects, the method chain will be executed in the order that the methods are listed—it doesn't use expression trees to store and translate the whole thing.

Calling OrderBythenWhere with linq-to-objects will, when you enumerate the results, sort the collection, then filter it. Conversely, filtering results with a call to Wherebefore sorting it with OrderBy will, when you enumerate, first filter, then sort. As a result the latter case can make a massive difference, since you'd potentially be sorting many fewer items.

Linq to objects does use deferred execution... Until you enumerate the results, no processing occurs in Linq to Objects either. It just doens't use expression trees to convert the entire method chain into a single operation, but rather directly executes the methods, each of which returns an enumerable which effectively runs as enumerated.
–
Reed CopseyDec 21 '11 at 19:39

@Reed - thanks. I did know that - not sure why I was thinking so stupidly. I +1'd you answer and corrected mine.
–
Adam RackisDec 21 '11 at 19:43

1

It's important to know - that's part of why people so often (even though it's not always good) add .ToList() - it forces an enumeration, which causes the entire operation to execute immediately. However, you can test this easily - just write collection.OrderBy(...) on a large collection, and ignore the results. You'll find it has nearly no execution cost.
–
Reed CopseyDec 21 '11 at 19:47

Because, with LINQ for SQL, the SQL grammar for SELECT mandates that the different clauses occur in a particular sequence. The compiler must generate grammatically correct SQL.

Applying LINQ for objects on an IEnumerable involves iterating over the IEnumerable and applying a sequence of actions to each object in the IEnumerable. Order matters: some actions may transform the object (or the stream of objects itself), others may throw objects away (or inject new objects into the stream).

The compiler can't divine your intent. It builds code that does what you said to do in the order in which you said to do it.

Linq to Objects does not reorder to avoid a would-be run-time step to do something that should be optimized at coding time. The resharpers of the world may at some point introduce code analysis tools to smoke out optimization opportunities like this, but it is definitely not a job for the runtime.

That's actually not true. While there is a conceptual model of how a SELECT query executes (e.g. compute the cartesian product of all involved tables, apply any JOIN criteria, apply the WHERE criteria, perform any GROUPing, apply the HAVING criteria, apply any ORDERing), but in the Real World, it would be a very naive (and poorly performing) SQL implementation that actually operated according to the conceptual model. So long as the results are equivalent, SQL implementations are free to act on a query as they see fit.
–
Nicholas CareyDec 21 '11 at 19:51