Why does this code generate a NotSupportedException?

Question

Why does this throw System.NotSupportedException?

string foo(string f) { return f; }
string bar = "";
var item = (from f in myEntities.Beer
            where f.BeerName == foo(bar)
            select f).FirstOrDefault();

Edit: Here's an MSDN reference that (kind of) explains things...

Any method calls in a LINQ to Entities query that are not explicitly mapped to a canonical function will result in a runtime NotSupportedException exception being thrown. For a list of CLR methods that are mapped to canonical functions, see CLR Method to Canonical Function Mapping.

See also http://mosesofegypt.net/post/LINQ-to-Entities-what-is-not-supported.aspx

Answer 1

The second version will fail as soon as you try to iterate over it. You can't use a locally defined method in an IQueryable<> where clause (of course, you can, but it will fail when the LINQ provider tries to translate it into SQL).

Answer 2

EDIT: Okay, the code blows up because it doesn't know what to do with the call to foo(). The query is built up as an expression tree which is then converted to SQL.

The expression tree translator knows about various things - such as string equality, and various other methods (e.g. string.StartsWith) but it doesn't know what your foo method does - foo() is a black box as far as it's concerned. It therefore can't translate it into SQL.

Answer 3

Because in the 2nd query no actual query is executed. Try adding ToList() where SingleOrDefault() is.

It's probably because the SQL-generation functionality isn't able to determine what to do with your foo() function, so can't generate output for it.