Dynamic WHERE clause in LINQ

Question

What is the best way to assemble a dynamic WHERE clause to a LINQ statement?

I have several dozen checkboxes on a form and am passing them back as: Dictionary<string, List<string>> (Dictionary<fieldName,List<values>>) to my LINQ query.

public IOrderedQueryable<ProductDetail> GetProductList(string productGroupName, string productTypeName, Dictionary<string,List<string>> filterDictionary)
{
    var q = from c in db.ProductDetail
            where c.ProductGroupName == productGroupName && c.ProductTypeName == productTypeName
            // insert dynamic filter here
            orderby c.ProductTypeName
            select c;
    return q;
}

Answer 1

This is the solution I came up with if anyone is interested.

https://kellyschronicles.wordpress.com/2017/12/16/dynamic-predicate-for-a-linq-query/

First we identify the single element type we need to use ( Of TRow As DataRow) and then identify the “source” we are using and tie the identifier to that source ((source As TypedTableBase(Of TRow)). Then we must specify the predicate, or the WHERE clause that is going to be passed (predicate As Func(Of TRow, Boolean)) which will either be returned as true or false. Then we identify how we want the returned information ordered (OrderByField As String). Our function will then return a EnumerableRowCollection(Of TRow), our collection of datarows that have met the conditions of our predicate(EnumerableRowCollection(Of TRow)). This is a basic example. Of course you must make sure your order field doesn’t contain nulls, or have handled that situation properly and make sure your column names (if you are using a strongly typed datasource never mind this, it will rename the columns for you) are standard.

Answer 2

This project on CodePlex have what you want.

System.Linq.Dynamic - http://dynamiclinq.codeplex.com/

Project Description

Extends System.Linq.Dynamic to support Execution of Lambda expressions defined in a string against Entity Framework or any provider that supports IQueryable.

As it is an extension of the source code you can find on Scott Guthrie's Blog it will allow you to do things like this:

And things like this:

Answer 3

You could use the Any() extension method. The following seems to work for me.

XStreamingElement root = new XStreamingElement("Results",
                from el in StreamProductItem(file)
                where fieldsToSearch.Any(s => el.Element(s) != null && el.Element(s).Value.Contains(searchTerm))
                select fieldsToReturn.Select(r => (r == "product") ? el : el.Element(r))
            );
            Console.WriteLine(root.ToString());

Where 'fieldsToSearch' and 'fieldsToReturn' are both List objects.