How to use PredicateBuilder with nested OR conditionals in Linq

Question

I've been very happily using PredicateBuilder but until now have only used it for queries with only either concatenated AND statements or OR statements. Now for the first time I need a pair of OR statements nested along with a some AND statements like this:

select x from Table1 where a = 1 AND b = 2 AND (z = 1 OR y = 2)

Using the documentation from Albahari, I've constructed my expression like this:

Expression<Func<TdIncSearchVw, bool>> predicate = 
    PredicateBuilder.True<TdIncSearchVw>(); // for AND
Expression<Func<TdIncSearchVw, bool>> innerOrPredicate = 
    PredicateBuilder.False<TdIncSearchVw>(); // for OR

innerOrPredicate = innerOrPredicate.Or(i=> i.IncStatusInd.Equals(incStatus));
innerOrPredicate = innerOrPredicate.Or(i=> i.RqmtStatusInd.Equals(incStatus));

predicate = predicate.And(i => i.TmTec.Equals(tecTm));
predicate = predicate.And(i => i.TmsTec.Equals(series));
predicate = predicate.And(i => i.HistoryInd.Equals(historyInd));

predicate.And(innerOrPredicate);
var query = repo.GetEnumerable(predicate);

This results in SQL that completely ignores the 2 OR phrases.

select x from  TdIncSearchVw 
    where ((this_."TM_TEC" = :p0 and this_."TMS_TEC" = :p1) 
    and this_."HISTORY_IND" = :p2)

If I try using just the OR phrases like:

Expression<Func<TdIncSearchVw, bool>> innerOrPredicate =
    PredicateBuilder.False<TdIncSearchVw>(); // for OR
innerOrPredicate = innerOrPredicate.Or(i=> i.IncStatusInd.Equals(incStatus));
innerOrPredicate = innerOrPredicate.Or(i=> i.RqmtStatusInd.Equals(incStatus));
var query = repo.GetEnumerable(innerOrPredicate);

I get SQL as expected like:

select X from TdIncSearchVw 
    where (IncStatusInd = incStatus OR RqmtStatusInd = incStatus)

If I try using just the AND phrases like:

predicate = predicate.And(i => i.TmTec.Equals(tecTm));
predicate = predicate.And(i => i.TmsTec.Equals(series));
predicate = predicate.And(i => i.HistoryInd.Equals(historyInd));
var query = repo.GetEnumerable(predicate);

I get SQL like:

select x from  TdIncSearchVw 
    where ((this_."TM_TEC" = :p0 and this_."TMS_TEC" = :p1) 
    and this_."HISTORY_IND" = :p2)

which is exactly the same as the first query. It seems like I'm so close it must be something simple that I'm missing. Can anyone see what I'm doing wrong here?

Thanks,

Terry

Answer 1

Shouldn't that be:

predicate = predicate.And(i => i.TmTec.Equals(tecTm));
predicate = predicate.And(i => i.TmsTec.Equals(series));
predicate = predicate.And(i => i.HistoryInd.Equals(historyInd));

predicate = predicate.And(innerOrPredicate);
var query = repo.GetEnumerable(predicate);

instead of (the OR predicate isn't getting set to the variable):

predicate = predicate.And(i => i.TmTec.Equals(tecTm));
predicate = predicate.And(i => i.TmsTec.Equals(series));
predicate = predicate.And(i => i.HistoryInd.Equals(historyInd));

predicate.And(innerOrPredicate);
var query = repo.GetEnumerable(predicate);