Dynamically changing a condition in a LINQ query

Question

The following Entity Framework query runs without errors.

Predicate<Program> filterProgram; if (programId.HasValue) filterProgram = (p => p.Id == programId && !p.IsDeleted); else filterProgram = (p => !p.IsDeleted); var analytics = (from a in repository.Query<Analytic>() where (a.Marker == "Open" || a.Marker == "LastTouch") && a.EntityType == "Proposal" && a.Site == "C" join p in repository.Query<Program>() on a.EntityId equals p.Id //where filterProgram(p) group a by new { a.LoginSessionId, a.EntityId, p.Id, p.Name } into g let f = g.OrderBy(x => x.TimestampUtc).FirstOrDefault(x => x.Marker == "Open") where f != null let t = g.FirstOrDefault(x => x.Marker == "LastTouch" && x.TimestampUtc > f.TimestampUtc) select new { ProgramId = g.Key.Id, Program = g.Key.Name, ProposalId = g.Key.EntityId, FirstOpen = f, LastTouch = (t ?? f).TimestampUtc }).ToList(); 

However, if I uncomment the line where filterProgram(p) , I get a runtime error:

In LINQ to Entities, the LINQ node type expression 'Invoke' is not supported.

I expected LINQ to be able to include my predicate in a query and convert it to SQL. Why am I getting this error, and is there a way to dynamically modify the predicate this way?

Answer 1

Change filterProgram to Expression<Func<Program, bool>> and then it should be used in the LINQ Where clause.

However, one caveat: I managed to get it to work in the method chain syntax, but not in the query syntax.

For example, this works:

 dataContext.Programs.Where (filterProgram) 

but this is not so:

 from p in dataContext.Programs where filterprogram 

(The compiler complains that it cannot solve the Where method, which is available for both IEnumerable and IQueryable .)

In your case, it may be acceptable to replace the string

 join p in repository.Query<Program>() 

from

 join p in repository.Query<Program>().Where(filterProgram) 

Answer 2

The problem is because the Entity Framework must be able to convert your LINQ query to SQL. Your LINQ query is compiled into a data structure called an expression tree, which is then passed to the Entity Framework for conversion to SQL.

You have two options:

• Replace your call with filterProgram a simpler C # expression. Depending on the complexity of filterProgram this may not be possible.
• Remove the filterProgram call and convert this request to IEnumerable<T> , possibly by calling .ToList() . Then you can filter the results of this query using filterProgram

Example 2

  var query = /* Your Query With filterProgram commented out */ var resultsFromSql = query.ToList(); var fullyFiltered = resultsFromSql.Select(filterProgram); 

Answer 3

In Linq to Entities, it is not possible to call an external method without converting the request to IEnumerable . Thus, since filterProgram is a method, you cannot call inside a request.

If possible, you can call ToList before calling filterProgram and it will work. Another option is perhaps inserting the filterProgram logic into the request.

Answer 4

The problem is that linq2entities is trying to translate the expression tree into SQL, in your expression tree there is a call to the Invoke method for the delegate type (filterProgram) however there is nothing stopping you from inserting this predicate

 var id= programId.HasValue ? programId.GetValueOrDefault() : -1; var analytics = (from a in repository.Query<Analytic>() where (a.Marker == "Open" || a.Marker == "LastTouch") && a.EntityType == "Proposal" && a.Site == "C" join p in repository.Query<Program>() on a.EntityId equals p.Id where !p.IsDeleted && (!hasValue || p.Id == id) .... 

This assumes -1 is invalid programId