Create a linq query to search for a contact, just like a smartphone does.

Question

Say there is a table in my database

                **Table Contact**
Id, FirstName, LastName,   Phone,       Email,           DateCreated
1   Tom        Williams    3052548623   [e-mail]    2013-12-21 14:51:08
etc...

I want users to be able to search for a contact by entering a string. Let's say a user enters:

tom       -> TRUE
tom wil   -> TRUE
wil tom   -> TRUE
tom XX    -> FALSE
t w 3 @   -> TRUE
wil 305   -> TRUE

(Truth means looking for the client Tom found, False means that he did not find it)

I will perform this type of search among different tables in my database. It will be nice if I do not need to create a query for a specific table.

---

The approach I'm thinking of is to split the search string every time I find one or more spaces. Then I will create n number of searches, and then perform the intersection?

Answer 1

- , , ( , Phone ).

, , ( ).

, -

public static Expression<Func<T, bool>> BuildPredicateForFilter<T>(string filterString)
        {
            //first, split search by space, removing white spaces, and putting this to upper case
            var filters = filterString.Split(new []{" "}, StringSplitOptions.RemoveEmptyEntries).Select(m => m.ToUpper());
            var parameter = Expression.Parameter(typeof (T), "m");

            //get string.Contains() method
            var containsMethod = typeof (string).GetMethod("Contains");
            //get string.ToUpper() method
            var toUpperMethod = typeof (string).GetMethod("ToUpper", new Type[]{});
            //find all the string properties of your class
            var properties = typeof(T).GetProperties().Where(m => m.PropertyType == typeof(string));
            //for all the string properties, build a "m.<PropertyName>.ToUpper() expression
            var members = properties.Select(p => Expression.Call(Expression.Property(parameter, p), toUpperMethod));
            Expression orExpression = null;
            //build the expression
            foreach (var filter in filters)
            {
                Expression innerExpression = null;
                foreach (var member in members)
                {
                    innerExpression = innerExpression == null
                        ? (Expression)Expression.Call(member, containsMethod, Expression.Constant(filter))
                        : Expression.OrElse(innerExpression, Expression.Call(member, containsMethod, Expression.Constant(filter)));
                }
                orExpression = orExpression == null
                    ? innerExpression
                    : Expression.AndAlso(orExpression, innerExpression);
            }
            return Expression.Lambda<Func<T, bool>>(orExpression, new[]{parameter});

        }

:

var result = <yourSource>.Where(Helper.BuildPredicateForFilter<TableName>("tom XX"));

, "tom XX", orExpression

((((m.FirstName.ToUpper().Contains("TOM") OrElse m.LastName.ToUpper().Contains("TOM")) OrElse m.Phone.ToUpper().Contains("TOM")) OrElse m.Email.ToUpper().Contains("TOM")) AndAlso (((m.FirstName.ToUpper().Contains("XX") OrElse m.LastName.ToUpper().Contains("XX")) OrElse m.Phone.ToUpper().Contains("XX")) OrElse m.Email.ToUpper().Contains("XX")))

public static IQueryable<T> FilterFor(this IQueryable<T> queryable, string filterString) {
  //same
  var predicate = Expression.Lambda<Func<T, bool>>(orExpression, new[]{parameter});
  return queryable.Where(predicate);
}

<yourSource>.FilterFor("tom XX");

Answer 2

, , , Contains. .

PredicateBuilder, , OR. PredicateBuilder, :

public static class PredicateBuilder
{
    public static Expression<Func<T, bool>> True<T>() { return f => true; }
    public static Expression<Func<T, bool>> False<T>() { return f => false; }

    public static Expression<Func<T, bool>> Or<T>(
        this Expression<Func<T, bool>> expr1,
        Expression<Func<T, bool>> expr2)
    {
        var secondBody = expr2.Body.Replace(expr2.Parameters[0], expr1.Parameters[0]);
        return Expression.Lambda<Func<T, bool>>
              (Expression.OrElse(expr1.Body, secondBody), expr1.Parameters);
    }

    public static Expression<Func<T, bool>> And<T>(
        this Expression<Func<T, bool>> expr1,
        Expression<Func<T, bool>> expr2)
    {
        var secondBody = expr2.Body.Replace(expr2.Parameters[0], expr1.Parameters[0]);
        return Expression.Lambda<Func<T, bool>>
              (Expression.AndAlso(expr1.Body, secondBody), expr1.Parameters);
    }
}

/, Expression :

internal class ReplaceVisitor : ExpressionVisitor
{
    private readonly Expression from, to;
    public ReplaceVisitor(Expression from, Expression to)
    {
        this.from = from;
        this.to = to;
    }
    public override Expression Visit(Expression node)
    {
        return node == from ? to : base.Visit(node);
    }
}

public static Expression Replace(this Expression expression,
    Expression searchEx, Expression replaceEx)
{
    return new ReplaceVisitor(searchEx, replaceEx).Visit(expression);
}

, , Compose. , , , .

public static Expression<Func<TFirstParam, TResult>>
    Compose<TFirstParam, TIntermediate, TResult>(
    this Expression<Func<TFirstParam, TIntermediate>> first,
    Expression<Func<TIntermediate, TResult>> second)
{
    var param = Expression.Parameter(typeof(TFirstParam), "param");

    var newFirst = first.Body.Replace(first.Parameters[0], param);
    var newSecond = second.Body.Replace(second.Parameters[0], newFirst);

    return Expression.Lambda<Func<TFirstParam, TResult>>(newSecond, param);
}

, . , , . , , Compose, , Contains , ORs .

public static IQueryable<T> AnyFieldContains<T>(
    this IQueryable<T> query,
    string searchText,
    params Expression<Func<T, string>>[] fieldSelectors)
{
    var filter = PredicateBuilder.False<T>();

    foreach (var selector in fieldSelectors)
    {
        filter = filter.Or(selector.Compose(
            value => value.Contains(searchText)));
    }
    return query.Where(filter);
}

, , , . , :

IQueryable<Foo> query = db.Foo;
string searchText = "wil tom";

var searchExpressions = searchText.Split(' ');

foreach (var expression in searchExpressions)
{
    query = query.AnyFieldContains(expression,
        foo => foo.FirstName,
        foo => foo.LastName,
        foo => foo.Phone);
}
var result = query.Any();

, ( , , , , , , - , , ), , , , . , , , "":

public static IQueryable<T> AnyFieldContains<T>(
    this IQueryable<T> query,
    string searchText)
{
    return AnyFieldContains(query, searchText,
        typeof(T).GetProperties()
        .Select(prop => CreateSelector<T>(prop))
        .ToArray());
}

private static Expression<Func<T, string>> CreateSelector<T>(PropertyInfo prop)
{
    var param = Expression.Parameter(typeof(T));
    Expression body = Expression.Property(param, prop);
    if (prop.PropertyType == typeof(decimal?))
        body = Expression.Call(body, typeof(SqlFunctions)
            .GetMethod("StringConvert", new[] { typeof(decimal?) }));
    else if (prop.PropertyType == typeof(double?))
        body = Expression.Call(body, typeof(SqlFunctions)
            .GetMethod("StringConvert", new[] { typeof(double?) }));
    return Expression.Lambda<Func<T, string>>(body, param);
}