Why one query works and not the other?

Question

I am using helper methods to pre-filter all of my queries based upon user access permissions.

Assuming a method signature of:

public IQueryable<Client> GetAllClients()

Why does this work when using LINQ:

IQueryable<Client> allItems = GetAllClients();
return (from item in allItems
where item.Name.Equals(name, StringComparison.InvariantCultureIgnoreCase)
select item).FirstOrDefault();

But not this:

return (from item in GetAllClients() 
    where item.Name.Equals(name, StringComparison.InvariantCultureIgnoreCase)
    select item).FirstOrDefault();

I am OK doing the first one, but having been away from LINQ for a few years, it would be nice to understand the why on this one.

By not working I mean that option 2 gives this exception:

A first chance exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll

Additional information: LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[typename] GetAllClients()' method, and this method cannot be translated into a store expression.

Clients is a data type stored in the database. I am creating methods on the entity framework data model for commonly used queries, and due to a multi-tenant design with security access defined by data type, I want to filter at the data access level.

Answer 1

The issue here is that the queryable layer is attempting to translate the method call GetAllClients into a query, rather than using the return value of GetAllClients as the source of a query. Syntactically deceiving, yes, but also completely expected.

This is happening because the IQueryable objects, unlike IEnumerables, are actually presenting metacode which can be used to translate (in this case) into SQL. Since there's no SQL equivalent for most C# methods, and compiled methods can't be scanned for their metacode the same way, such frameworks simply error out when they encounter something they cannot translate.

Note that one way to avoid much of this issue is to avoid using the Linq styntax, and instead make method calls, which will be slightly less deceiving:

return GetAllClients()
    .Where(item => item.Name.Equals(name, StringComparison.InvariantCultureIgnoreCase)
    .FirstOrDefault();

Answer 2

You're returning an IQueryable<Client> from your filter method. The C# compiler recognizes that and automatically converts the lambda expression in your return statement into an expression tree representation of it. (That automatic conversion is how the entire delayed execution feature of IQueryable works).

In the first case return item from allItems you get an expression tree like this:

Call: Queryable.Select(Constant: allItems, LambdaExpression: predicate)

In the second case return item from GetAllClients() you get this instead:

Call: Queryable.Select(Call: GetAllClients, LambdaExpression: predicate)

Notice that the first argument to Queryable.Select is different! In the second case, the compiler delays executing the call to GetAllClients by storing it in the expression tree. When that expression tree eventually gets to EF's SQL translator, EF does not know how to change a call to the C# GetAllClients function into valid SQL.