Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Linq Dynamically append Where clauses with OR

I've been trying to build a linq query with dynamically added where clauses. I have a web page with a bunch of checkboxes that are selected to correspond to which fields you want to search:

enter image description here

What I have so far is the following:

//This calls a select from table to construct the query which the where clauses will be added to
IQueryable<AutoCompleteRestultDto> query = GetAutocompleteResults();

if (FirstName == true || AllFields == true)
{
    Expression<Func<AutoCompleteRestultDto, bool>> firstNameFilter = c => terms.Any(t => c.FirstName.ToLower().Contains(t.ToLower()));
    query = query.Where(firstNameFilter);
}
if (LastName == true || AllFields == true)
{
    Expression<Func<AutoCompleteRestultDto, bool>> lastNameFilter = c => terms.Any(t => c.LastName.ToLower().Contains(t.ToLower()));
    query = query.Where(lastNameFilter);
}
if (KnownAs == true || AllFields == true)
{
    Expression<Func<AutoCompleteRestultDto, bool>> knownAsFilter = c => terms.Any(t => c.KnownAs.ToLower().Contains(t.ToLower()));
    query = query.Where(knownAsFilter);
}
// etc.

return query
       .Select(c => new ContactAutoCompleteModel
                {
                    label = c.FirstName + " " + c.LastName
                })
                .Take(15)
                .OrderBy(d => d.label)
                .ToList();

The problem is this solution requires all the expressions that are tacked on to simultaneously be true ie: where(clause1 AND clause2 AND clause3)

Can't figure out how to change this to OR clauses ie: where(clause1 OR clause2 OR clause3)

like image 740
Carel Avatar asked Dec 26 '22 03:12

Carel


1 Answers

You are chaining Enumerable.Where calls in the code you posted. That's why you get the AND effect. This below is a poach of PredicateBuilder using predicates instead of expressions.

  public static class PredicateExtensions
  {
    public static Predicate<T> Or<T> (this Predicate<T> p1, Predicate<T> p2)
    {
      return obj => p1(obj) || p2(obj);
    }

    public static Predicate<T> And<T> (this Predicate<T> p1, Predicate<T> p2)
    {
      return obj => p1(obj) && p2(obj);
    }
    public static Predicate<T> False<T> () { return obj => false; }
    public static Predicate<T> True<T>  () { return obj => true; }

    public static Predicate<T> OrAll<T> (IEnumerable<Predicate<T>> conditions)
    {
      Predicate<T> result = PredicateExtensions.False<T>();
      foreach (Predicate<T> cond in conditions)
        result = result.Or<T>(cond);
      return result;
    }

    public static Predicate<T> AndAll<T> (IEnumerable<Predicate<T>> conditions)
    {
      Predicate<T> result = PredicateExtensions.True<T>();
      foreach (Predicate<T> cond in conditions)
        result = result.And<T>(cond);
      return result;
    }
  }

You can use the above like so with an enumerable , customizing (apriori) the predicate enumerable on some condition:

Predicate<AutoCompleteRestultDto> firstNamePredicate = 
    c => terms.Any(t => c.FirstName.ToLower().Contains(t.ToLower()));
Predicate<AutoCompleteRestultDto> lastNamePredicate = 
    c => terms.Any(t => c.LastName.ToLower().Contains(t.ToLower()));
Predicate<AutoCompleteRestultDto> knownAsPredicate = 
    c => terms.Any(t => c.KnownAs.ToLower().Contains(t.ToLower()));
var all = new Predicate<AutoCompleteRestultDto>[] { 
    firstNamePredicate, 
    knownAsPredicate, 
    lastNamePredicate };
//
var items = query.Where(a => PredicateExtensions.OrAll(all)(a)).ToList();
items = query.Where(a => PredicateExtensions.AndAll(all)(a)).ToList();

or adding them iteratively like you do, step by step:

Predicate<AutoCompleteRestultDto> orResultPredicate = 
    PredicateExtensions.False<AutoCompleteRestultDto>();
if (FirstName == true || AllFields == true) {
    orResultPredicate=orResultPredicate.Or(firstNamePredicate); } 
if (LastName == true || AllFields == true) { 
    orResultPredicate = orResultPredicate.Or(lastNamePredicate); }
if (KnownAs == true || AllFields == true) { 
    orResultPredicate = orResultPredicate.Or(knownAsPredicate); }
Func<AutoCompleteRestultDto, bool> funcOr = c => orResultPredicate(c);
//
IQueryable<AutoCompleteRestultDto> query; // initialized already
var items = query.Where(funcOr).ToList(); 
like image 69
andrei.ciprian Avatar answered Dec 27 '22 17:12

andrei.ciprian