ASP.NET search form - dynamic Linq to SQL?

Question

I have a search form that allows users to search on several different fields in several different ways. Here is an example of my code.

var claims = from c in db.Claims select c;

switch (ddlSearchField.Text)
{
    case "StartsWith":
        claims = claims.Where(c => c.companyFileID.StartsWith(txtSearchBox.Text));
        break;

    case "Equals":
        claims = claims.Where(c => c.companyFileID == txtSearchBox.Text);
        break;

    case "Contains":
        claims = claims.Where(c => c.companyFileID.Contains(txtSearchBox.Text));
        break;
}

I have about ten different fields that a user can search on so my outer switch statement is pretty large. There has to be a more elegant way to accomplish this.

Thomas Levesque · Accepted Answer · 2009-10-12 23:24:54Z

3

You could refactor some parts of the code by creating an extension method. Something like that :

static class QueryableExtensions
{
    private static MethodInfo StringContainsMethod;
    private static MethodInfo StringStartsWithMethod;

    static QueryableExtensions()
    {
        Type[] singleStringParam = new[] {typeof(string)};
        StringContainsMethod = typeof(string).GetMethod("Contains", singleStringParam);
        StringStartsWithMethod = typeof(string).GetMethod("StartsWith", singleStringParam);
    }

    public static IQueryable<T> AppendTextFilter<T>(this IQueryable<T> queryable, Expression<Func<T, string>> memberSelector, string condition, string value)
    {
        Expression expression = null;
        switch (condition)
        {
            case "StartsWith":
                expression = Expression.Call(
                                memberSelector.Body,
                                StringStartsWithMethod,
                                Expression.Constant(value));
                break;

            case "Equals":
                expression = Expression.Equal(
                                memberSelector.Body,
                                Expression.Constant(value));
                break;

            case "Contains":
                expression = Expression.Call(
                                memberSelector.Body,
                                StringContainsMethod,
                                Expression.Constant(value));
                break;

            default:
                throw new NotSupportedException(string.Format("'{0}' is not a supported condition", condition));
        }

        var lambda = Expression.Lambda<Func<T, bool>>(
                        expression,
                        memberSelector.Parameters);
        return queryable.Where(lambda);
    }
}

You could then use it like that :

var claims = db.Claims
             .AppendTextFilter(c => c.companyFileID, ddlSearchField.Text, txtSearchBox.Text)
             .AppendTextFilter(c => c.someOtherProperty, ddlOtherSearchField.Text, txtOtherSearchBox.Text)
             ...;

edited Oct 12, 2009 at 23:24

answered Oct 12, 2009 at 20:21

Thomas Levesque

294k73 gold badges639 silver badges769 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

tom d Over a year ago

Hi Thomas, your solution looks great and it is very close to what I am looking for. Is it safe to set the "claims" var to a gridview's datasource? I just tried that and received an error message.

tom d Over a year ago

Sorry, the error message was: Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL.

Thomas Levesque Over a year ago

OK, I see the problem... Linq to SQL can't convert a delegate call to a SQL expression, so it fails. I'll try to find a solution and update my answer

Thomas Levesque Over a year ago

OK, I fixed the code to use expressions rather than delegates. I only tested it with a list, but I think it should work fine with Linq to SQL. Please let me know !

dahlbyk Over a year ago

Very nice. If you want == instead of .Equals(), you can use Expression.Equal(memberSelector.Body, Expression.Constant(value)).

|

Paul Abbott · Accepted Answer · 2009-10-12 20:19:25Z

0

Have you tried dynamic LINQ? It allows you to build string-based query statements similar to a SQL WHERE clause.

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

answered Oct 12, 2009 at 20:19

Paul Abbott

7,2213 gold badges30 silver badges47 bronze badges

Comments

dahlbyk · Accepted Answer · 2009-10-12 20:20:18Z

0

You could make a dictionary of your possible predicates:

Dictionary<string, Func<string, Expression<Func<Claim, bool>>>> map = new Dictionary<string, Func<string, Expression<Func<Claim, bool>>>>() {
    { "StartsWith", t => c => c.companyFileID.StartsWith(t) },
    { "Equals",     t => c => c.companyFileID == t },
    { "Contains",   t => c => c.companyFileID.Contains(t) }
};

Which could be used like this:

var search = ddlSearchField.Text;
var text = txtSearchBox.Text;

var claims = from c in db.Claims select c;

Func<string, Expression<Func<Claim, bool>>> predicate = null;
if(dict.TryGetValue(search, out predicate))
    claims = claims.Where(predicate(text));

answered Oct 12, 2009 at 20:20

dahlbyk

78k8 gold badges105 silver badges126 bronze badges

Collectives™ on Stack Overflow

ASP.NET search form - dynamic Linq to SQL?

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related