IQueryable OrderBy with Func<TModel, TValue> : what's happening

Question

With the code given from this question OrderBy is not translated into SQL when passing a selector function

Func<Table1, string> f = x => x.Name;
var t = db.Table1.OrderBy(f).ToList();

The translated SQL is:

SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[Name] AS [Name]
FROM [dbo].[Table1] AS [Extent1]

OK.

I can understand that the code compiles : IQueryable inherits from IEnumerable, which have an OrderBy method taking a Func<TModel, TValue> as parameter.

I can understand that the ORDER BY clause is not generated in SQL, as we didn't pass an Expression<Func<TModel, TValue>> as the OrderBy parameter (the one for IQueryable)

But what happens behind the scene ? What happens to the "wrong" OrderBy method ? Nothing ? I can't see how and why... Any light in my night ?

Answer 1

Because f is a delegate rather than an expression, the compiler picks the IEnumerable OrderBy extension method instead of the IQueryable one.

This then means that all the results are fetched from the database, because the ordering is then done in memory as if it were Linq to Objects. That is, in-memory, the ordering can only be done by fetching all the records.

Of course, in reality this still doesn't actually happen until you start enumerating the result - which in your case you do straight away because you eager-load the result with your call to ToList().

Update in response to your comment

It seems that your question is as much about the IQueryable/IEnumerable duality being 'dangerous' from the point of view of introducing ambiguity. It really isn't:

t.OrderBy(r => r.Field)

C# sees the lambda as an Expression<> first and foremost so if t is an IQueryable then the IQueryable extension method is selected. It's the same as a variable of string being passed to an overloaded method with a string and object overload - the string version will be used because it's the best representation.

As Jeppe has pointed out, it's actually because the immediate interface is used, before inherited interfaces

t.AsEnumerable().OrderBy(r => r.Field)

C# can't see an IQueryable any more, so treats the lambda as a Func<A, B>, because that's it's next-best representation. (The equivalent of only an object method being available in my string/object analogy before.

And then finally your example:

Func<t, string> f = r => r.Field;
t.OrderBy(f);

There is no possible way that a developer writing this code can expect this to be treated as an expression for a lower-level component to translate to SQL, unless the developer fundamentally doesn't understand the difference between a delegate and an expression. If that's the case, then a little bit of reading up solves the problem.

I don't think it's unreasonable to require a developer to do a little bit of reading before they embark on using a new technology; especially when, in MSDN's defence, this particular subject is covered so well.

I realise now that by adding this edit I've now nullified the comment by @IanNewson below - but I hope it provides a compelling argument that makes sense :)

Answer 2

But what happens behind the scene?

Assuming db.Table1 returns an Table<Table1>, the compiler will:

• Check whether Table<T> has an OrderBy method - nope
• Check whether any of the base classes or interfaces it implements has an OrderBy method - nope
• Start looking at extension methods

It will find both Queryable.OrderBy and Enumerable.OrderBy as extension methods which match the target type, but the Queryable.OrderBy method isn't applicable, so it uses Enumerable.OrderBy instead.

So you can think of it as if the compiler has rewritten your code into:

List<Table1> t = Enumerable.ToList(Enumerable.OrderBy(db.Table1, f));

Now at execution time, Enumerable.OrderBy will iterate over its source (db.Table1) and perform the appropriate ordering based on the key extraction function. (Strictly speaking, it will immediately return an IEnumerable<T> which will iterate over the source when it's asked for the first result.)