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 } 

Then I want to execute the SQL query "WHERE ... IN ...." on the results. This works great using the following. (return results with ID 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 do does the same operation, but you do not need to specify the i.ID part. Therefore, if I have a field name string, I want to apply the WHERE IN clause, how can I use this in the .Contains () method?

Answer 1

There are several ways to do this. One way is to use Dynamic Linq . Another way is to use Predicate Builder .

Answer 2

You need to build an expression tree. It will look like this (partial code, will not compile). This one and the other contains and is equal. 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 left = Expression.Property(param, filterType.GetProperty(propertyName)); var right = Expression.Constant(propertyValue, modelMetaData.ModelType); LambdaExpression predicate = null; if (searchFilterAttribute.FilterType == FilterType.Contains) { var methodContains = typeof(string).GetMethod("Contains", new[] { typeof(string) }); var filterContains = Expression.Call(left, methodContains, right); predicate = Expression.Lambda(filterContains, param); } else { 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 can rewrite this in a multiple extension method (it is too specific for this project at the moment) and a blog about it.