How to use PredicateBuilder with nested OR conditionals in Linq
- by tblank
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
