Is there a way to parameterize method in a linq query?

Question

In my application with Linq to SQL, the user can search for text. An asterix (*) can be used at the beginning and/or end of the search expression. The code now is this:

var search = SearchTextBox.Text.Trim();
bool filterStartsWith = false, filterEndsWith = false;
if (!string.IsNullOrEmpty(search))
{
    filterStartsWith = search.EndsWith("*");
    filterEndsWith = search.StartsWith("*");
    if (filterStartsWith) search = search.Substring(0, search.Length - 1);
    if (filterEndsWith) search = search.Substring(1);

    if (filterStartsWith)
    {
        if (filterEndsWith)
        {
            query = query.Where(item => item.Omschrijving.Contains(search));
        }
        else
        {
            query = query.Where(item => item.Omschrijving.StartsWith(search));
        }
    }
    else
    {
        if (filterEndsWith)
        {
            query = query.Where(item => item.Omschrijving.EndsWith(search));
        }
        else
        {
            query = query.Where(item => item.Omschrijving == search);
        }
    }
}

However, I want to generalize this, because this kind of search happens on more places. Also, some tables, this should happen on more than one column. Any ideas?

I use Visual Studio 2010 with .NET Framework 4.0.

score 3 · Accepted Answer · answered Oct 29 '13 at 11:56

You could try this:

static IQueryable<T> WhereColumnContains<T>(this IQueryable<T> source, Expression<Func<T, string>> selector, string search)
{
    if (string.IsNullOrWhiteSpace(search))
    {
        return source;
    }

    Expression<Func<T, bool>> expression;

    search = search.Trim();

    var filterStartsWith = search.EndsWith("*");
    var filterEndsWith = search.StartsWith("*");

    if (filterEndsWith) search = search.Substring(1);

    if (filterStartsWith)
    {
        search = search.Substring(0, search.Length - 1);

        if (filterEndsWith)
        {
            var parameter = Expression.Parameter(typeof(T), "parameter");

            expression = Expression.Lambda<Func<T, bool>>(
                Expression.Call(Expression.Invoke(selector, parameter), typeof(string).GetMethod("Contains", new[] { typeof(string) }), Expression.Constant(search)),
                parameter);
        }
        else
        {
            var parameter = Expression.Parameter(typeof(T), "parameter");

            expression = Expression.Lambda<Func<T, bool>>(
                Expression.Call(Expression.Invoke(selector, parameter), typeof(string).GetMethod("StartsWith", new[] { typeof(string) }), Expression.Constant(search)),
                parameter);
        }
    }
    else
    {
        if (filterEndsWith)
        {
            var parameter = Expression.Parameter(typeof(T), "parameter");

            expression = Expression.Lambda<Func<T, bool>>(
                Expression.Call(Expression.Invoke(selector, parameter), typeof(string).GetMethod("EndsWith", new[] { typeof(string) }), Expression.Constant(search)),
                parameter);
        }
        else
        {
            var parameter = Expression.Parameter(typeof(T), "parameter");

            expression = Expression.Lambda<Func<T, bool>>(
                Expression.Equal(Expression.Invoke(selector, parameter), Expression.Constant(search)),
                parameter);
        }
    }

    return source.Where(expression);
}

Call it as follows:

query = query.WhereColumnContains(item => item.Omschrijving, search);

This is probably what I need, I think. Is it also possible to search multiple columns, with an Or relation? My Lambda knowledge is a bit rusty ;-) — doekman, Oct 29 '13 at 13:36
@doekman An AND relation is always easier of course, you can just chain the calls together :-) But an OR is certainly possible, using `Expression.OrElse(...)`. The annoying bit is figuring out how to pass in the parameters. I'll leave that as an excercise for the reader if you don't mind. My answer should provide enough clues to get you there, I hope. — Kris Vandermotten, Oct 29 '13 at 13:41
Thanks for the tip. I didn't think of `Expression.OrElse`. I think I just pass them in with `params`. — doekman, Oct 29 '13 at 13:45
Do you have one `search` value to apply to many columns? That should be easy. Like you said, just pass seach as the first parameter, then `params` the expression. If you were to have a different `search` value for every column (n columns, n values), that's where it could get messy. — Kris Vandermotten, Oct 29 '13 at 13:51
Same search for all columns, but now the `.Where` method needs an `Expression`, but `.OrElse` gives an BinaryExpression... — doekman, Oct 29 '13 at 14:14

score 0 · Answer 2 · answered Oct 29 '13 at 09:59

You can use a Strategy Pattern. You will have 4 strategies based on the "search" value and with a "bool CanHandle(search)" method, a factory the create a List of strategies, and your program will call a client that simply create a new factory, call a method "BuildQuery(search)" that execute the right strategy found with the CanHandle method, and return a query value.

http://en.wikipedia.org/wiki/Strategy_pattern

score 0 · Answer 3 · answered Oct 29 '13 at 11:10

You could use dynamically built expressions.

Here is a sample code to implement StartWith on multiple columns (may not compile, i'm typing it directly in stackoverflow)- just add support to other methods...

This code works on all linq-to-sql queries, assuming "columnsToSearch" is always refering to string properties.

IQueryable myQuery = ... your query you want to filter ...;
string searchWhat = "text to search";
bool startsWith;
Expression condition = null;
var p = Expression.Parameter(myQuery.ElementType,"x");
foreach (string column in columnsToSearch)
{
    if (startsWith)
    {
        var myCondition = Expression.Call(Expression.PropertyOrField(p, column),
                          typeof (string).GetMethod("StartsWith"),
                          Expression.Constant(searchWhat));
        if (condition == null)
            condition = myCondition;
        else
            condition = Expression.OrElse(condition, myCondition);
     }
 }
 var newQuery = Expression.Call(typeof (Queryable).GetMethod("Where"), myQuery.Expression, Expression.Lambda(condition, p));
 var myQueryFiltered = myQuery.Provider.CreateQuery(newQuery);

In a nutshell, if you had a query myRepository.Where(t=>t.Mycondition) this code will generate a new query like myRepository.Where(t=>t.Mycondition).Where(t=>t.Field1.StartsWith("a")||t.Field2.StartWith("a")) (depending on which columns you gave to it of course)

Then, all you have to do is to cast back "myQueryFiltered" to IEnumerable or IQueryable in order to execute it an get filtered results :)

Is there a way to parameterize method in a linq query?

3 Answers3

Linked