Using custom methods that return IQueryable in the LINQ to entities

Question

I have two methods that return IQueryable:

IQueryable<Person> GetGoodPeople();

and

IQueryable<Person> GetBadPeople(); 

I need to write this query:

var q = from x in GetGoodPeople()
        from y in GetBadPeople()
        select new { Good = x, Bad = y };

The above code is not supported in the linq to entities (the NotSupportedException is thrown), except I declare a variable and use it in the query:

var bad = GetBadPeople()
var q = from x in GetGoodPeople()
        from y in bad
        select new { Good = x, Bad = y };

Is there a way that I can use IQueryable methods in the linq to entities directly?

Answer 1

Short answer - it's not possible feasible. Your fix is the correct way to solve the problem.

Once entity framework (and LINQ2Sql as well) begins parsing the expression tree, it's too late. The call to GetBadPeople() is actually lazily executed, and as such, is attempted to be converted into SQL itself.

Here's what it may look like:

Table(Person).Take(1).SelectMany(x => value(UserQuery).GetBadPeople(), (x, y) => new <>f__AnonymousType0`2(Good = x, Bad = y))

Here, I've written GetGoodPeople() as simply returning People.Take(1). Note how that query is verbatim, but GetBadPeople() contains a function call.

Your workaround of evaluating GetBadPeople() outside of the expression is the correct solution. This causes the expression tree to call Expression.Constant(bad), rather than attemping to invoke GetBadPeople().

That makes the query look like this:

Table(Person).Take(1).SelectMany(x => value(UserQuery+<>c__DisplayClass1_0).bad, (x, y) => new <>f__AnonymousType0`2(Good = x, Bad = y))

Note there's no method invocation here - we simply pass in the variable.