Dynamically Modifying Where Condition in LINQ Query

Question

The following Entity Framework query runs without error.

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 the run-time error:

The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.

I was expecting that LINQ would be able to incorporate my predicate into the query and convert it to SQL. Why am I getting this error, and is there a way to dynamically modify a where predicate this way?

Answer 1

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

You have two options:

1. Replace your call to filterProgram with a more basic C# expression. Depending on the complexity of filterProgram this may not be possible.
2. Remove you call to filterProgram, and convert this query to an IEnumerable<T>, maybe by calling .ToList(). You can then further filter the results of this query using filterProgram

Example of 2

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

Answer 2

Change filterProgram's type to Expression<Func<Program, bool>> and then it should be usable in a LINQ Where clause.

One caveat, however: I managed to get it to work in method chain syntax, but not in query syntax.

For example, this works:

dataContext.Programs.Where (filterProgram)

but this does not:

from p in dataContext.Programs
where filterprogram 

(The compiler complains that it cannot resolve method Where, which is available on both IEnumerable and IQueryable.)

In your case, it might be acceptable to replace the line

join p in repository.Query<Program>()

with

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