Dynamic "WHERE IN" on IQueryable (linq to SQL)

Question

I have a LINQ to SQL query returning rows from a table into an IQueryable object.

IQueryable<MyClass> items = from table in DBContext.MyTable
select new MyClass
{
 ID = table.ID,
 Col1 = table.Col1,
 Col2 = table.Col2
}

I then want to perform a SQL "WHERE ... IN ...." query on the results. This works fine using the following. (return results with id's ID1 ID2 or ID3)

sQuery = "ID1,ID2,ID3";
string[] aSearch = sQuery.Split(',');
items = items.Where(i => aSearch.Contains(i.ID));

What I would like to be able to do, is perform the same operation, but not have to specify the i.ID part. So if I have the string of the field name I want to apply the "WHERE IN" clause to, how can I use this in the .Contains() method?

Answer 1

There's a couple of ways to do this. One way is to use Dynamic Linq. Another way is to use Predicate Builder.

Answer 2

Your have to build an expression tree. It will end up looking like this (partial code, will not compile). This one does both contains and equals. Used in this project: http://weblogs.asp.net/rajbk/archive/2010/04/15/asp-net-mvc-paging-sorting-filtering-a-list-using-modelmetadata.aspx

 var param = Expression.Parameter(filterType, propertyName);
                    var right = Expression.Constant(propertyValue, modelMetaData.ModelType);
                    var left = Expression.Property(param, filterType.GetProperty(propertyName));

                    LambdaExpression predicate = null;

                    if (searchFilterAttribute.FilterType == FilterType.Contains) //FilterType is Contains
                    {
                        var methodContains = typeof(string).GetMethod("Contains", new[] { typeof(string) });
                        var filterContains = Expression.Call(left, methodContains, right);
                        predicate = Expression.Lambda(filterContains, param);
                    }
                    else //FilterType is Equals
                    {
                        var expr = Expression.Equal(left, right);
                        predicate = Expression.Lambda(expr, param);

                    }

                    var expression = Expression.Call(typeof(Queryable), "Where", new Type[] { queryable.ElementType },
                             queryable.Expression,
                             predicate);
                    queryable = queryable.Provider.CreateQuery<T>(expression);

I may rewrite this into a reusable extension method (it is too specific to that project at the moment) and blog about it.