Func vs Expression – The LINQ expression node type Invoke is not supported in LINQ to Entities

I had an odd problem, the resolution of which exposed an interesting bit of information about what goes on under the C# covers that we never usually know.

I had a Linq query something like this…

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

This worked fine, but as I had 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();
}

This could then be called as follows…

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

However, this gave a run-time exception “System.NotSupportedException: ‘The LINQ expression node type ‘Invoke’ is not supported in LINQ to Entities.'”

If you search for information, you’ll find a lot of people with the same problem, usually caused by them trying to pass a C# method into SQL Server. The following would be a good example…

List<int> ids = ctx
  .Where(a => HasLooseKnepple(a))
  .Select(a => a.ID))
  .ToList();

HasLooseKnepple() is a local C# method. When Entity Framework attempts to convert the Linq query to SQL, it does not know anything about HasLooseKnepple(), and assumes it is something that SQL Server will understand. It uses Invoke() to call the method in the database. This causes a run-time exception, as SQL Server says “Don’t ask me chum, I have no idea what a knepple is either!”

Small aside

If you do want to do something like this, you’ll need to enumerate the query before you use your C# method. Enumerating causes the data to be pulled from the database, leaving the rest of the code to be executed in memory, where your C# method can be called without problem.

However, you should be aware that this can cause a significant performance hit if you are pulling a lot of data from the database with the enumeration. Therefore, you should try and do as much filtering as possible before you enumerate, for example…

List<int> ids = ctx
  .Where(a => /* some other filtering here */)
  .ToList() // Pulls the data from the database into memory
  .Where(a => HasLooseKnepple(a)) // Filtered in memory
  .Select(a => a.ID))
  .ToList();

The first call to ToList() enumerates the query, and after that, everything, including the call to HasLooseKnepple() takes place in memory.

End of small aside

All of which is very interesting, but didn’t help me one bit, for two reasons:

  1. The lambda I was using in my Where clause can easily be translated into SQL, so Entity Framework had no need to call Invoke() on the code.
  2. If the code in the lambda couldn’t have been translated into SQL, it wouldn’t have worked in the first query shown above.

After a bit more searching, I discovered that if I changed the DoAdvancedSearch() method as follows…

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

…it worked fine. Note that I have changed the type of the lambda being passed in from a Func<MyType, bool> to an Expression<Func<MyType, bool>>.

In the vague recesses of my mind, I had some idea what the difference was, but couldn’t see why it made a difference here. It took some asking around and reading, but the explanation was well worth the effort.

Func vs Expression

A Func is basically a delegate, meaning that it’s a pointer to some executable IL code. It should now be obvious why SQL Server didn’t know what to do with it, as it can’t execute IL (yet, I reckon by SQL Server 2118 it will be able to, but that’s still in beta!!).

An Expression is a object graph that represents the code in a way that describes what it does. Being a data structure, you could manipulate it and modify the behaviour at run time if you wanted. As it happens, I was playing around with some code to solve problems by genetic programming, and that’s exactly what was being done there. I was building some random expressions, executing them to see which gave the best answer to the problem, cross-breeding and mutating them in an attempt to get a better solution. Having the algorithm represented as a data structure allowed me to modify it at run time.

The important point for us is that Entity Framework understands expressions. When it sees one in a query, it attempts to translate it into SQL. In the case of lambdas like the one I showed earlier, it can generate pure SQL, and so the first query runs without problem. The trick here is knowing (which I didn’t) that when you pass a lambda to a Where clause (or any other Linq extension method), you’re actually passing in an Expression (with a capital E as we’re talking about the .NET type, as opposed to the general concept), not a Func.

However, when I wrote the first version of my method, I marked the type of the incoming lambda as a Func<MyType, bool>. This caused it to be compiled into a Func and passed to SQL Server, which complained. When I changed the parameter type to Expression<Func<MyType, bool>>, the lambda was passed in as an Expression, and so Entity Framework was able to translate it into SQL without problem.

This isn’t the sort of thing you’d hit every day, but as it’s not the first time I’ve found a difference between Func and Expression, I thought it worth noting (if only so I don’t forget it next time!).

Be First to Comment

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.