Build dynamic Expression for search

I'm in trouble, can't figure out seems a very simple thing that in plain SQL can be done within 1 minute, it's been several hours so far. Here is the situation:

• I have single field where user may enter as many words as he/she likes
• I need to build Expression to find match
• Let's say there are 3 fields in database: firstname, middlename, lastname
• I need to split the search entry and compare against those 3 fields
• I'm dealing with Silverlight RIA, EF
• Once again search entry contains UNKNOWN number of words

Here under what I'm trying to accomplish, return type is mandatory:

public Expression<Func<MyEntity, bool>> GetSearchExpression(string text)
{
    Expression<Func<MyEntity, bool>> result;

    var keywords = text.Trim().Split(" ");
    foreach(var keyword in keywords)
    {
         // TODO: 

         // check whether 'OR' is required (i.e. after second loop)
         // (firstname = 'keyword'
         // AND
         // middlename = 'keyword'
         // AND
         // lastname = 'keyword')
         // OR
         // (firstname like '%keyword%'
         // AND
         // middlename like '%keyword%'
         // AND
开发者_C百科         // lastname like '%keyword%')

    }
    return result;
}

Thanks in advance!

---

The simplest thing would be to use Joe Albahari's PredicateBuilder to do something like this:

var predicate = PredicateBuilder.False<MyEntity>();

foreach (string keyword in keywords)
{
    string temp = keyword;
    predicate = predicate.Or (
        p => p.FirstName.Contains (temp) && 
             p.LastName.Contains (temp) &&
             p.MiddleName.Contains (temp));
}

return predicate;

I left out the equality-checks because "Contains" (i.e. like '%...%') will cover that possibility anyway.

I do have to point out, though, that your conditions don't make any sense, from a business logic standpoint. Under what circumstances do you want to find someone whose first, last, and middle name all contain "John"? I suspect what you really want is something more like this:

var predicate = PredicateBuilder.True<MyEntity>();

foreach (string keyword in keywords)
{
    string temp = keyword;
    predicate = predicate.And (
        p => p.FirstName.Contains (temp) || 
             p.LastName.Contains (temp) ||
             p.MiddleName.Contains (temp));
}

return predicate;

One final note: Because PredicateBuilder requires you to call .AsExpandable() when you are creating your query, I don't know whether this will work for you. You might have to resort to building your own expressions, which can be somewhat tedious. This can get you started, though:

var pParam = Expression.Parameter(typeof(MyEntity), "p");
var predicate = Expression.Constant(true);
foreach (string keyword in keywords)
{
    var keywordExpr = Expression.Constant(keyword);
    // TODO: create an expression to invoke .FirstName getter
    // TODO: create an expression to invoke string.Contains() method
    //TODO: do the same for lastname and middlename

    predicate = Expression.And(predicate, 
        Expression.Or(
            Expression.Or(firstNameContainsKeyword,
                middleNameContainsKeyword),
            lastNameContainsKeyword));
}
return Expression.Lambda<Func<MyEntity, bool>>(predicate, pParam);