Concatenate string members using Linq PredicateBuilder for text search

Question

I have a REST WebAPI using EntityFramework database first. All code is generated off the EDMX file, entities, repository classes and API controllers etc.

I have added some filtering functionality which allows users to add conditions via the query string that translate to LinqKit PredicateBuilder / Linq expressions that filter results when hitting the db.

e.g. /api/Users?FirstName_contains=Rog

This will return all users with 'Rog' in the User.FirstName member. This uses PredicateBuilder to dynamically build an appropriate Linq expression to then use as a Where clause against the DbSet.

For example:

var fieldName = "FirstName";
var value = "Rog";

var stringContainsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) });

var parameter = Expression.Parameter(typeof(User), "m");
var fieldAccess = Expression.PropertyOrField(parameter, fieldName);
var fieldType = typeof(User).GetProperty(fieldName, BindingFlags.IgnoreCase | BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public).PropertyType;

var expression = Expression.Lambda<Func<User, bool>>(Expression.Call(fieldAccess, stringContainsMethod, Expression.Constant(value, fieldType))
    , parameter)

var andPredicate = PredicateBuilder.True<User>();
andPredicate = andPredicate.And(expression);

var query = Db.Users
    .AsQueryable()
    .AsExpandable()
    .Where(andPredicate);

Now the problem. I want the client to be able to match results based on a composition of members.

e.g. /api/Users?api_search[FirstName,LastName]=Rog

i.e. search first name + last name for matches of 'Rog', so I could search for 'Roger Sm' and get a result for first name = Roger and last name = Smith.

If I was to query the DbSet using fluent it would look like:

users.Where(u => (u.FirstName + " " + u.LastName).Contains("Rog"));

What I am struggling with is creating a predicate / linq expression that will handle the concatenation of string members FirstName + " " + LastName dynamically.

even though there is a CONCAT function in t-sql, I don't think EF (specifically L2SQL) functionality supports this translation. You should pull all entries first with .ToList() and then do Selection in memory. — DevilSuichiro, Jul 03 '16 at 08:25
Hmm really? From my travels I am pretty sure that the above fluent example will translate through to T-SQL of `WHERE FirstName + ' ' + LastName LIKE '%Rog%'`. — pstep, Jul 03 '16 at 08:31
Why not `u => u.FirstName.Contains(query) || u.LastName.Contains(query)`? — haim770, Jul 03 '16 at 08:39
Because that wouldn't work with a query of say 'Roger Smith'. — pstep, Jul 03 '16 at 09:05

score 1 · Accepted Answer · answered Jul 03 '16 at 15:30

PredicateBuilder is not really needed here.

The string concatenation expression can be generated using string.Concat method call which is supported by EF:

static Expression<Func<T, string>> GenerateConcat<T>(IEnumerable<string> propertyNames)
{
    var parameter = Expression.Parameter(typeof(T), "e");
    // string.Concat(params string[] values)
    var separator = Expression.Constant(" ");
    var concatArgs = Expression.NewArrayInit(typeof(string), propertyNames
        .SelectMany(name => new Expression[] { separator, Expression.PropertyOrField(parameter, name) })
        .Skip(1));
    var concatCall = Expression.Call(typeof(string).GetMethod("Concat", new[] { typeof(string[]) }), concatArgs);
    return Expression.Lambda<Func<T, string>>(concatCall, parameter);
}

The string contains predicate can be generated by simple string.Contains method call:

static Expression<Func<T, bool>> GenerateContains<T>(Expression<Func<T, string>> member, string value)
{
    var containsCall = Expression.Call(member.Body, "Contains", Type.EmptyTypes, Expression.Constant(value));
    return Expression.Lambda<Func<T, bool>>(containsCall, member.Parameters);
}

Combining them together with your example:

var predicate = GenerateContains(GenerateConcat<User>(new[] { "FirstName", "LastName" }), "Rog");

Thanks Ivan - brilliant. I have changed the accepted answer and yes you are correct the question was really just about creating the appropriate lambda expression. PredicateBuilder just handles the chaining of my various expressions for execution against the collection. — pstep, Jul 03 '16 at 22:04

score 0 · Answer 2 · answered Jul 03 '16 at 09:32

0

Try the following (I haven't tested it against a database):

public class User
{
public string FirstName { get; set; }
public string LastName { get; set;}
}
void Main()
{
    List<User> users = new List<User> { 
                           new User { FirstName = "john", LastName = "smith" }, 
                           new User { FirstName = "siler", LastName = "johnston" } };
    string searchName = "ja smi";
    String[] terms = searchName.Split(' '); 
    var items = users.Where(x => terms.Any(y => x.FirstName.Contains(y)) 
                              || terms.Any(y => x.LastName.Contains(y)));
}

answered Jul 03 '16 at 09:32

Peter Smith

5,528
8
51
77

Thanks. Splitting up the terms and doing an Any / Or based search is definitely a fall back if I can't achieve the concatenation. Now that I think about it I might try this to see if it gives a good enough result. – pstep Jul 03 '16 at 09:43
I have accepted this as the way forward, although it was technically already possible with my functionality albeit verbose e.g. `/api/Users?or_firstName_contains=ja&or_firstName_contains=smi&or_lastName_contains=ja&or_lastName_contains=smi`. I do know it must be possible because Linq already handles it using regular fluent or query syntax however this should get me across the line for now. – pstep Jul 03 '16 at 10:40

Concatenate string members using Linq PredicateBuilder for text search

2 Answers2