none
Reusing Where expressions? RRS feed

  • Question

  • Hi,

    For some (probably historical) reason the users of my app expect wildcards to be implicit in their search fields. That is, if they type "112" in the Phone Number search field, they expect the search to be "*112*". If they, on the other hand, does put in a wildcard themselves, they expect that to take precedence. This means that if they type "112*" they expect only Phone Numbers starting with "112" to be returned. Nothing really fancy there.

    This would not be a real problem if I did not have a dozen or so of these fields. For the Phone Number field I did the following, which works like a charm:


    Code Snippet

         var dataContext = new TrsDataContext();

    IQueryable<SubscriptionSearch> searchQuery =  from subscription in new  TrsDataContext().SubscriptionSearches                                                                                    select subscription;

                if (phoneNumber.Length > 0)
                {
                    if (phoneNumber.IndexOfAny(new Char[] { '*', '?' }) >= 0)
                    {
                        string searchString = phoneNumber.Replace('*', '%');
                        searchString = searchString.Replace('?', '_');
                        searchQuery = searchQuery.Where(s => SqlMethods.Like(s.PhoneNumber, searchString));
                    }
                    else
                    {
                        searchQuery = searchQuery.Where(s => s.PhoneNumber.Contains(phoneNumber));
                    }
                }
                return searchQuery;



    What I would like to have is a generalized method that somehow encapsulates the functionality in the "if (PhoneNumber.Length > 0)" block, so I could write something like:


    searchQuery = searchQuery.Wildcard(s => s.PhoneNumber, phoneNumber);

    Or just something so I don't have to repeat all that code 10 times, which is cumbersome and rather ugly.

    Any ideas? I've run out... ;-)

    Thanks!
    Torben Rahbek Koch
    Sunday, June 15, 2008 10:41 AM

Answers

  • The ideal method to do what you would like to do is possible to implement. Take a look at the following code:


    Code Snippet

        public static class Helper
        {
            public static IQueryable<T> WildCard<T>(this IQueryable<T> query, Expression<Func<T, string>> leftSide, string wildcardString)
            {
                if (string.IsNullOrEmpty(wildcardString))
                {
                    return query;
                }

                Expression<Func<T, bool>> whereExpression = null;

                if (wildcardString.IndexOfAny(new char[] { '*', '?' }) >= 0)
                {
                    wildcardString = wildcardString.Replace('*', '%').Replace('?', '_');
                    whereExpression = Expression.Lambda<Func<T, bool>>(Expression.Call(typeof(SqlMethods).GetMethod("Like", new Type[] { typeof(string), typeof(string) }), leftSide.Body, Expression.Constant(wildcardString)), leftSide.Parameters[0]);
                }
                else
                {
                    whereExpression = Expression.Lambda<Func<T, bool>>(Expression.Call(leftSide.Body, typeof(string).GetMethod("Contains", new Type[] { typeof(string) }), Expression.Constant(wildcardString)), leftSide.Parameters[0]);
                }

                return query.Where(whereExpression);
            }
        }

    Monday, June 16, 2008 11:19 AM

All replies

  • Hi Torben,

     

    The following function should do what you want. I wrote it as a static method, you can refactor it easily into an extension method if you want so:

     

    Code Snippet

    public static IQueryable<Customer> WildCard(IQueryable<Customer> searchQuery, Func<Customer, string> getFunction, string input)

    {

    if (input.Length > 0)

    {

    string searchString;

    if (input.IndexOfAny(new Char[] { '*', '?' }) >= 0)

    {

    searchString = input.Replace('*', '%');

    searchString = searchString.Replace('?', '_');

    }

    else

    {

    searchString = String.Format(CultureInfo.InvariantCulture, "%{0}%", input);

    }

    return searchQuery.Where(s => SqlMethods.Like(getFunction(s), searchString));

    }

    return null;

    }

     

     

     

    And you can consume this following will do:

     

    Code Snippet

    customers = WildCard(customers, (Customer c) => c.Phone, phoneNumber);

    customers = WildCard(customers, (Customer c) => c.Address, address);

     

     

    If you want to get the exact usage you wanted, you need to make the function an extension method by adding "this" to the first parameter, than you will not need to pass the "customers" each time because it will operate on the current instance it is called on.

     

    Hope this helps,

     

    Sidar 

    Monday, June 16, 2008 4:03 AM
  • Hi Sidar,

     

    Thanks for trying! I should probably have mentioned that I have been down that road before - including a couple of variants - the result of which usually is the dreaded:

     

    Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL (or some other variant of this)

     

    The problem is, of course due to deferred execution, that the compiler builds an expression tree out of the souce. This expression tree contains the call to the WildCard function, which LinqToSql have no clue what to do about.

     

    I generally like the deferred execution idea, but here it does spoil my plans somewhat.

     

    Perhaps writing a custom QueryProvider would do the trick? I'm not certain about this, though, any hints before I take on that task?

     

    Thanks for listening!

    Torben

    Monday, June 16, 2008 8:35 AM
  • Hi again,

     

    I figured out another way to do it, somewhat less elegant than I wanted, but none the less it works pretty well.

     

    Having this methods:

     

    Code Snippet

    static string Wildcard(string searchValue)

    {

    if (searchValue.Length > 0)

    {

    string searchString;

    if (searchValue.IndexOfAny(new Char[] { '*', '?' }) >= 0)

    {

    searchString = searchValue.Replace('*', '%');

    searchString = searchString.Replace('?', '_');

    }

    else

    {

    searchString = String.Format(CultureInfo.InvariantCulture, "%{0}%", searchValue);

    }

    return searchString;

    }

    return searchValue;

    }

     

     

    I can now do this:

    Code Snippet

    var q = from s in ctx.SubscriptionSearches

    where Phone == "" || SqlMethods.Like(s.PhoneNumber, Extensions.Wildcard((Phone)))

    select s;

     

     

    This works!

    Monday, June 16, 2008 11:11 AM
  • That's what I said in the first message Smile

     

    I'll try to look what is the problem you are having with the extensions in the evening, as i have a running example of that at home. (but didn't copied -thought that'll make the post more complicated than needed).

     

    Happy that it worked anyway, thanks.
    Monday, June 16, 2008 11:17 AM
  • The ideal method to do what you would like to do is possible to implement. Take a look at the following code:


    Code Snippet

        public static class Helper
        {
            public static IQueryable<T> WildCard<T>(this IQueryable<T> query, Expression<Func<T, string>> leftSide, string wildcardString)
            {
                if (string.IsNullOrEmpty(wildcardString))
                {
                    return query;
                }

                Expression<Func<T, bool>> whereExpression = null;

                if (wildcardString.IndexOfAny(new char[] { '*', '?' }) >= 0)
                {
                    wildcardString = wildcardString.Replace('*', '%').Replace('?', '_');
                    whereExpression = Expression.Lambda<Func<T, bool>>(Expression.Call(typeof(SqlMethods).GetMethod("Like", new Type[] { typeof(string), typeof(string) }), leftSide.Body, Expression.Constant(wildcardString)), leftSide.Parameters[0]);
                }
                else
                {
                    whereExpression = Expression.Lambda<Func<T, bool>>(Expression.Call(leftSide.Body, typeof(string).GetMethod("Contains", new Type[] { typeof(string) }), Expression.Constant(wildcardString)), leftSide.Parameters[0]);
                }

                return query.Where(whereExpression);
            }
        }

    Monday, June 16, 2008 11:19 AM
  • Hi CompuBoy,

     

    Amazing! Thanks, this works right out of the box. I have been playing with Expression.Lambda and Expression.Call but could not make it work. Seeing your example it's no wonder why ;-)

     

    This will function as a template on how to do this, I hope.

     

    Thanks again!

    Torben

     

     

    Monday, June 16, 2008 11:54 AM