How-to use predicates for customer where LINQ query in Entity Framework, Extension Method "Search by Words"

Question

I would like to build a custom linq query using predicates.

Instead of using a static Where clause, i.e. ...

public IEnumerable<Entities.BusinessDirectory.BDEntity> Search(string searchExpression)
{
    var db = new ApplicationDbContext();
    return db.BDEntities
             .Where(x => searchExpression.Split(' ').Any(y => x.Value.Contains(y)));
}

... I am extending the DbContext with the generic method Search(predicate, searchExpression), which shall have the same effect as the static method (see above)

public static class DbContextExtensions
{
    public static IEnumerable<T> Search<T>(
        this DbSet<T> dbSet, 
        Expression<Func<T, object>> predicate, 
        string searchExpression)
      where T : class, new()
    {
        //Experimental
        return dbSet
            .Where(x => searchExpression.Split(' ').Any(y => predicate.Contains(x)));
    }
}

However, I receive the following error:

Error 6 Instance argument: cannot convert from 'System.Linq.Expressions.Expression>' to 'System.Linq.ParallelQuery' Extensions.cs

Answer 1

The simple answer is that you may just add a predicate to a query Where clause, bear in mind that the definition of a Where predicate is that it returns bool. Ultimately your expression must be a boolean expression in order to be added into a Where clause.

Expression<Func<BDEntity, bool>> predicate = (BDEntity entity) => entity.Field == "TEST";
var query = context.BDEntities;

if (predicate != null)
    query = query.Where(predicate);

// expand to get the results.
var results = query.ToList();

dynamic expression

If you need to build up an expression like _ => _.MyFieldValue == "TEST" at runtime, that's a little more tricky.

Given a predicate _ => _.DatabaseField and a string value "TEST", create an expression suitable for entity framework to create a where clause that tests the predicate.

This method will build an appropriate Equal expression.

public static Expression<Func<T, bool>> BuildEqualPredicate<T>(
    Expression<Func<T, string>> memberAccessor,
    string term)
{
    var toString = Expression.Convert(Expression.Constant(term), typeof(string));
    Expression expression = Expression.Equal(memberAccessor.Body, toString);
    var predicate = Expression.Lambda<Func<T, bool>>(
        expression,
        memberAccessor.Parameters);
    return predicate;
}

The following assertions will pass:

var expression = Predicate.BuildEqualPredicate<MyDbEntity>(_ => _.DatabaseField, "TEST");
expression.Compile()
    .Invoke(new MyDbEntity { DatabaseField = "TEST" })
    .Should().BeTrue();
expression.Compile()
    .Invoke(new MyDbEntity { DatabaseField = "ANOTHERVALUE"})
    .Should().BeFalse();

The predicate can be used by entity framework by including the predicate in a where clause.

var predicate = PredicateExtensions.BuildEqualPredicate<MyDbEntity>(
    _ => _.DatabaseField,
    "TEST");
var results = context.DbEntity.Where(predicate).ToList();

additionally

You can do a little more with the member expression, like using StartsWith and Contains to create more complex predicates.

Expression<Func<T, string>> memberAccessor = _ => _.DBEntityField;

You can dynamically create a predicate like this, in this example it creates a predicate that checks whether or not a string StartsWith a particular value, or is null.

private Expression<Func<T, bool>> BuildPredicate(
    string term,
    Expression<Func<T, string>> memberAccessor)
{
    var startsWith = typeof(string).GetMethod("StartsWith", new[] { typeof(string) });
    var isNull = typeof(string).GetMethod("IsNullOrEmpty", new[] { typeof(string) });
    var toString = Expression.Convert(Expression.Constant(term), typeof(string));
    Expression expression = Expression.Call(
        memberAccessor.Body,
        startsWith,
        new Expression[] { toString });
    expression = Expression.Or(expression, Expression.Call(
        isNull,
        toString));
    var predicate = Expression.Lambda<Func<T, bool>>(
        expression,
        memberAccessor.Parameters);
    return predicate;
}

This will give you an expression that evaluates to this:

Expression<Func<BDEntity, bool>> expression = _ => 
   string.IsNullOrEmpty(term)
   || _.DBEntityField.StartsWith(term)

This expression has several advantages:

• it can be converted into suitable SQL
• all records are returned when term is null or empty
• StartsWith ensures that an appropriate index can be used.

The expression may be similarly added to the query with query = query.Where(expression), replacing StartsWith with Contains will search for any occurrences within the string, but you will forgo any SQL index optimizations, so it's contraindicated.