'The LINQ expression node type 'Invoke' is not supported in LINQ to Entities' when lambda is passed as a parameter, but not when used directly

Question

Before anyone votes to close this as a duplicate of this, this and many other similar questions, please read the question carefully as I don't think it is (even though it looks very similar).

I have a Linq query as follows...

List<int> ids = ctx
  .Where(a => a.PartInformationTypeID == pitID && vals.Contains(a.PartDefinitionID))
  .Select(a => a.SystemID)
  .Distinct()
  .ToList();

...where pitID is an int and vals is a List<int>

This works fine, but as I have four such queries, only differing by the lambda in the Where clause, I thought it would be better to pull the code out into a common method...

private List<int> DoAdvancedSearch(Func<MyType, bool> p)
{
  return ctx
    .Where(a => p(a))
    .Select(a => a.SystemID)
    .Distinct()
    .ToList();
}

I could then call this as follows...

List<int> ids = DoAdvancedSearch(systemIDs,
                 a => a.PartInformationTypeID == pitID && vals.Contains(a.PartDefinitionID))

However, this method gives a run-time exception "System.NotSupportedException: 'The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.'"

Having read loads of other questions with the same exception, I managed to solve it by changing the method as follows...

private List<int> DoAdvancedSearch(Expression<Func<MyType, bool>> p)
{
  return ctx
    .Where(p)
    .Select(a => a.SystemID)
    .Distinct()
    .ToList();
}

However, one thing I can't seem to find is why my first query above (with the lambda in the Where clause) didn't work, wheras the second query in the extracted method did? I don't think it's an issue with the actual lambda, as it doesn't contain anything that EF can't translate into SQL (which it demonstrably does with the first version), so it's evidently something to do with the fact that the lambda was passed as a Func rather than an Expression.

The closest I found to an explanation was in the answer to this question, but that was based around the fact that the code being passed in couldn't be translated into SQL. As I said, I don't think that's the problem here, as EF managed to translate it fine in the first code snippet.

Anyone able to explain why my example doesn't work with a Func?

As a secondary question, anyone able to explain why .Where(a => p(a)) gave a compiler error of "Method name expected" on p, but was fine with .Where(p)? I thought these were equivalent.

Thanks

Answer 1

An Expression object is compiled into a data structure (an expression tree). This is converted to SQL code at runtime by EF. A Func on the other hand is converted to executable IL code by the compiler. When you ask EF to translate a query that contains Where(x => f(x)), you have a Func f which is IL code, and a small expression tree around that, describing a call to the function represented by f. What the error message is saying is that this "Invoke" expression tree cannot be translated to SQL, which is reasonable since what is being invoked is a piece of IL code.

Note that in your first example where the Where call is inline, you're using an Expression, not a Func. This is because lambda expressions in C# can have both types, and when using the Where extension method on IQueryable, the parameter is of an Expression type, thus the entire lambda is compiled to an expression tree.