"LINQ to Entities does not recognize the method" on IQueryable.Where

Question

This compiles, but at runtime it throws an exception.

using (var edmx = new MyEDMX())
{
    Expression<Func<SearchResult, int, bool>> pred = (sr, idx) =>
            sr.Foo >= searchParams.MinFoo && sr.Foo <= searchParams.MaxFoo
        ;
    return edmx.SearchResults.Where(pred).ToList();
}

The exception is a System.NotSupportedException:

LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[MyNamespace.SearchResult] Where[SearchResult](System.Linq.IQueryable`1[MyNamespace.SearchResult], System.Linq.Expressions.Expression`1[System.Func`3[MyNamespace.SearchResult,System.Int32,System.Boolean]])' method, and this method cannot be translated into a store expression.

I think the problem has something to do with the Int32 parameter to the predicate expression, because the code works perfectly at runtime if I substitute the following predicate:

Expression<Func<SearchResult, bool>> pred = (sr) =>
        sr.Foo >= this.MinFoo && sr.Foo <= this.MaxFoo
    ;
return edmx.SearchResults.Where(pred).ToList();

Without the Int32 parameter for the predicate, that resolves to a different overload of Queryable<TSource>.Where(). It returns correctly filtered results without throwing an exception.

The Int32 version is known to Intellisense, it compiles, and it's documented for the version of .NET I'm using, .NET 4.5. I get the same exception whether or not the expression actually does anything with the Int32 idx parameter.

Usually "LINQ to Entities does not recognize the method" means you're calling a method that has no equivalent in whatever the query is being translated into -- SQL in this case.

Is it that LINQ to SQL doesn't know how to create row numbers on LINQ to SQL queries? The EDMX is serving (wrapping, reflecting, exposing, whatever) a SQL Server view.

Does anybody have a guess about what's going wrong here?

Anything I need to do can be done using Skip/Take with the overload that works, but I'm curious.

Well, you've got the answer already -- the EF provider that you're using didn't implement a translation for that overload of `Where`. Seems like the SQL generation would be pretty complicated, so someone probably decided it wasn't worth the work. — jjj, Nov 10 '15 at 20:14
@jjj Well, I was hoping it might be something obvious I was doing wrong. Dang. — 15ee8f99-57ff-4f92-890c-b56153, Nov 10 '15 at 20:26

Paul Tsai · Answer 1 · 2016-10-27T22:52:36.910

The func provided expects 2 parameters, however, there is only one. The code below, first creates an anonymous type on SearchResults. The anonymous type has the search result and the index value. We can then pass that into the IQueryable;

using (var edmx = new MyEDMX())
{
    var pred = (sr, idx) =>
        sr.Foo >= searchParams.MinFoo && sr.Foo <= searchParams.MaxFoo;
    return edmx.SearchResults
        .Select((v, i) => new {result = v, index = i})
        .Where( w=> pred(w.result, w.index)).ToList();
}

"LINQ to Entities does not recognize the method" on IQueryable.Where

1 Answers1