CODEWITHSUNDEEP
c#entity-frameworklinqpredicatebuilder
John S
John S

Reputation: 8351

Building a Linq to EF query to a variable list of keywords in a variable number of columns?

I am trying to come up with a utility method to build a Linq Query or Linq Predicate to add to an Linq to EF query to do search for all terms in a list of terms in a variable number of columns.

I am trying to use PredicateBuilder to build the where clause. With one search term and a fixed list of columns it is relatively easy. The pseudo code that I am trying to work up looks like this so far:

private static Predicate<Project> CreateDynamicSearch(IEnumerable<strings> searchableColumns, string[] searchTerms)
{
      var predicate = PredicateBuilder.True<Project>();
      foreach (var columnName in searchableColumns) 
      { 
        foreach (var term in searchTerms)
        {
          predicate = predicate.And(a => a.**columnName**.Contains(term));
        }
       predicate = predicate.Or(predicate);
      }
      return predicate;
}

The biggest issue I have is handling the expression for the columnName. Previous advice was to use an expression tree but I do not understand how that works into this scenario.

** Update ** I've taken the code as you have it after the update. It builds but when I actually make the call it errors on the Extension.Property(param,columnName); line, with the error Instance property 'Name' is not defined for type 'System.Func`2[Myclass,System.Boolean]' message. The columnName = "Name"

** Update 2 ** The way it's called: 

var test = CreateDynamicSearch<Func<Project, bool>>(searchCols, searchTerms);

Upvotes: 1

Views: 290

Answers (1)

Evk
Evk

Reputation: 101613

You can build expression for predicate yourself, in this case it's relatively easy:

private static Expression<Func<T, bool>> CreateDynamicSearch<T>(IEnumerable<string> searchableColumns, string[] searchTerms) {
    // start with true, since we combine with AND
    // and true AND anything is the same as just anything
    var predicate = PredicateBuilder.True<T>();
    foreach (var columnName in searchableColumns) {                
        // start with false, because we combine with OR
        // and false OR anything is the same as just anything
        var columnFilter = PredicateBuilder.False<T>();
        foreach (var term in searchTerms) {
            // a =>
            var param = Expression.Parameter(typeof(T), "a");
            // a => a.ColumnName
            var prop = Expression.Property(param, columnName);
            // a => a.ColumnName.Contains(term)
            var call = Expression.Call(prop, "Contains", new Type[0], Expression.Constant(term));
            columnFilter = columnFilter.Or(Expression.Lambda<Func<T, bool>>(call, param));
        }
        predicate = predicate.And(columnFilter);
    }
    return predicate;
}

In response to comment

I was just curious if there was some way you could combine the expression created by Expression.Property(param, columnName) with the one the compiler generates for (string s) -> s.Contains(term)

You can do that with (for example) like this:

// a =>
var param = Expression.Parameter(typeof(T), "a");                    
// a => a.ColumnName
var prop = Expression.Property(param, columnName);                    
// s => s.Contains(term)
Expression<Func<string, bool>> contains = (string s) => s.Contains(term);
// extract body - s.Contains(term)
var containsBody = (MethodCallExpression)contains.Body;                    
// replace "s" parameter with our property - a.ColumnName.Contains(term)
// Update accepts new target as first parameter (old target in this case is 
// "s" parameter and new target is "a.ColumnName")
// and list of arguments (in this case it's "term" - we don't need to update that).
// 
var call = containsBody.Update(prop, containsBody.Arguments);
columnFilter = columnFilter.Or(Expression.Lambda<Func<T, bool>>(call, param));

Upvotes: 2

Related Questions