Use Expression Trees to build a dynamic query that acts like (SELECT property FROM table WHERE property Like '%value1%' OR property Like '%value2%' ... )

In my previous post, I had a chance to work with "OrderBy" extension done by Adams Anderson and Marc Gravell (click here for details). This sample was my inspiration to write this blog which uses the same technique to solve my problem that has been bothering me for a while.

Basically, I was not able to find a simple way to dynamically build the following SQL statement using LINQ (Lambda style):

SELECT name, title FROM user WHERE title Like '%program%' OR title Like '%admin%' 

Of course, if I have a list of "exact job titles", I can do something like this:

SELECT name, title FROM user WHERE title IN ('application programmer', 'system administrator')

which can be expressed in LINQ:

string[] filters = {"application programmer", "system administrator"};

var result = dbcontext.USERS.Where(u => filters.Contains(u.title));

However, I need to dynamically build the query with Where method along with Like and Or operators. Basically, I am going to create a new extension called "WhereLikeIn" (I could not come up with a better name :) which uses expression trees to construct a query against an IQueryable data source.

First, create a WhereLikeIn calss as shown below:

public static class WhereLikeInHelper
    {
        // string, string[]
        public static IEnumerable<T> WhereLikeIn<T>(this IEnumerable<T> enumerable, string prop, string[] strings)
        {
            return enumerable.AsQueryable().WhereLikeIn(prop, strings).AsEnumerable();
        }

        // string, string[]
        public static IQueryable<T> WhereLikeIn<T>(this IQueryable<T> collection, string prop, string[] strings)
        {
            collection = ApplyWhereLikeIn<T>(collection, prop, strings);

            return collection;
        }

        // expression x => x.property
        public static IEnumerable<T> WhereLikeIn<T, TValue>(this IEnumerable<T> enumerable, Expression<Func<T, TValue>> expression, params TValue[] collectionparam)
        {
            return enumerable.AsQueryable().WhereLikeIn(expression, collectionparam).AsEnumerable();
        }

        // expression x => x.property
        public static IEnumerable<T> WhereLikeIn<T, TValue>(this IEnumerable<T> enumerable, Expression<Func<T, TValue>> expression, IEnumerable<TValue> collectionparam)
        {
            return enumerable.AsQueryable().WhereLikeIn(expression, collectionparam).AsEnumerable();
        }

        // expression x => x.property
        public static IQueryable<T> WhereLikeIn<T, TValue>(this IQueryable<T> collection, Expression<Func<T, TValue>> expression, params TValue[] collectionparam)
        {
            return WhereLikeIn(collection, expression, (IEnumerable<TValue>)collectionparam);
        }

        // expression x => x.property
        public static IQueryable<T> WhereLikeIn<T, TValue>(this IQueryable<T> collection, Expression<Func<T, TValue>> expression, IEnumerable<TValue> collectionparam)
        {
            
            if (expression == null) throw new ArgumentException("lambda expression is missing");
            if (collectionparam == null) throw new ArgumentException("parameters missing");

            if (typeof(TValue) != typeof(string)) throw new ArgumentException("property must be in string type.");


            // convert it to string
            string[] strings = collectionparam.Cast<string>().ToArray();

            string prop = ((MemberExpression)(expression.Body)).Member.Name;

            collection = ApplyWhereLikeIn<T>(collection, prop, strings);
            

            return collection;
        }

        private static IQueryable<T> ApplyWhereLikeIn<T>(IQueryable<T> collection, string prop, string[] strings)
        {
            Type _type = typeof(T);

            // s => s.property
            ParameterExpression _arg = Expression.Parameter(_type, "s");
            Expression _expr = _arg;

            PropertyInfo _pi = _type.GetProperty(prop);
            _expr = Expression.Property(_expr, _pi);
            _type = _pi.PropertyType;
            
            // Is string
            if (_type != typeof(string)) throw new ArgumentException("property must be in string type.");

            // Is strings emptyornull
            if(strings == null)
                throw new ArgumentException("parameters must be in string[] type.");
            else if(strings.Length == 0)
                throw new ArgumentException("parameters must contain at least one string.");

            // [SAMPLE] => foreach(s in strings){ s => s.property.Contains(s) }
            // s => s.property.Contains(one_string)
            Expression _param = Expression.Constant(strings[0]);
            Expression _predicateBody = Expression.Call(_expr, typeof(string).GetMethod("Contains"), _param); // _arg
            for(int i = 1; i < strings.Length; i++){
                Expression _p = Expression.Constant(strings[i]);
                Expression _cmpr = Expression.Call(_expr, typeof(string).GetMethod("Contains"), _p);
                _predicateBody = Expression.OrElse(_cmpr, _predicateBody);
            }
           
            MethodCallExpression whereCallExpression = Expression.Call(
                typeof(Queryable),
                "Where",
                new Type[] { collection.ElementType },
                collection.Expression,
                Expression.Lambda<Func<T, bool>>(_predicateBody, new ParameterExpression[] { _arg })
                );

            ////
            return collection.Provider.CreateQuery<T>(whereCallExpression);

        }
    }

Now, I can use this extension to dynamically build the following SQL query:

SELECT name, title FROM user WHERE title Like '%program%' OR title Like '%admin%' 

public ActionResult GetDepartmentStaff(string filters)
{
     // filters format: "program;admin"
     //
     string[] _filters = filters != null ? filters.Split(';') : null;

     var _staff = from p in db.People
                  select p;
    
    if(_filters != null)
    {
         // p.title => "title" 
         _staff = _staff.WhereLikeIn("title", _filters);

         // [Alternative ways]
         // _staff = _Staff.WhereLikeIn(s => s.title, _filters.AsEnumerable());
         // _staff = _Staff.WhereLikeIn(s => s.title, "program", "admin");
    }

    return Json(_staff.ToList(),JsonRequestBehavior.AllowGet);

}

:)