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>> (specifically, 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

I have similar scenario where I need to add filters based on the user input and I chain the where clause.

Here is the sample code.

var votes = db.Votes.Where(r => r.SurveyID == surveyId);
if (fromDate != null)
{
    votes = votes.Where(r => r.VoteDate.Value >= fromDate);
}
if (toDate != null)
{
    votes = votes.Where(r => r.VoteDate.Value <= toDate);
}
votes = votes.Take(LimitRows).OrderByDescending(r => r.VoteDate);

Answer 2

A simple Approach can be if your Columns are of Simple Type like String

public static IEnumerable<MyObject> WhereQuery(IEnumerable<MyObject> source, string columnName, string propertyValue)
{
   return source.Where(m => { return m.GetType().GetProperty(columnName).GetValue(m, null).ToString().StartsWith(propertyValue); });
}

Answer 3

It seems much simpler and simpler to use the ternary operator to decide dynamically if a condition is included

List productList = new List();

        productList =
                db.ProductDetail.Where(p => p.ProductDetailID > 0 //Example prop
                && (String.IsNullOrEmpty(iproductGroupName) ? (true):(p.iproductGroupName.Equals(iproductGroupName)) ) //use ternary operator to make the condition dynamic
                && (ID == 0 ? (true) : (p.ID == IDParam))
                ).ToList();

Answer 4

I came up with a solution that even I can understand... by using the 'Contains' method you can chain as many WHERE's as you like. If the WHERE is an empty string, it's ignored (or evaluated as a select all). Here is my example of joining 2 tables in LINQ, applying multiple where clauses and populating a model class to be returned to the view. (this is a select all).

public ActionResult Index()
    {
        string AssetGroupCode = "";
        string StatusCode = "";
        string SearchString = "";

        var mdl = from a in _db.Assets
                  join t in _db.Tags on a.ASSETID equals t.ASSETID
                  where a.ASSETGROUPCODE.Contains(AssetGroupCode)
                  && a.STATUSCODE.Contains(StatusCode)
                  && (
                  a.PO.Contains(SearchString)
                  || a.MODEL.Contains(SearchString)
                  || a.USERNAME.Contains(SearchString)
                  || a.LOCATION.Contains(SearchString)
                  || t.TAGNUMBER.Contains(SearchString)
                  || t.SERIALNUMBER.Contains(SearchString)
                  )
                  select new AssetListView
                  {
                      AssetId = a.ASSETID,
                      TagId = t.TAGID,
                      PO = a.PO,
                      Model = a.MODEL,
                      UserName = a.USERNAME,
                      Location = a.LOCATION,
                      Tag = t.TAGNUMBER,
                      SerialNum = t.SERIALNUMBER
                  };


        return View(mdl);
    }

Answer 5

Just to share my idea for this case.

Another approach by solution is:

public IOrderedQueryable GetProductList(string productGroupName, string productTypeName, Dictionary> filterDictionary)
{
    return db.ProductDetail
        .where
        (
            p =>
            (
                (String.IsNullOrEmpty(productGroupName) || c.ProductGroupName.Contains(productGroupName))
                && (String.IsNullOrEmpty(productTypeName) || c.ProductTypeName.Contains(productTypeName))
                // Apply similar logic to filterDictionary parameter here !!!
            )
        );  
}

This approach is very flexible and allow with any parameter to be nullable.

Answer 6

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.

Answer 7

System.Linq.Dynamic might help you build LINQ expressions at runtime.

• The dynamic query library relies on a simple expression language for formulating expressions and queries in strings.
• It provides you with string-based extension methods that you can pass any string expression into instead of using language operators or type-safe lambda extension methods.
• It is simple and easy to use and is particularly useful in scenarios where queries are entirely dynamic, and you want to provide an end-user UI to help build them.

Source: Overview in Dynamic LINQ

The library lets you create LINQ expressions from plain strings, therefore, giving you the possibility to dynamically build a LINQ expression concatenating strings as you require.

Here's an example of what can be achieved:

var resultDynamic = context.Customers
    .Where("City == @0 and Age > @1", "Paris", 50)
    .ToList();

Answer 8

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 9

Union method can be used to create dynamic OR conditions in LINQ.

Suppose we need the following sql:

select * 
from employees 
where payroll starts with 'abc' or  payroll starts with '34565' ...

Then it can be done like this

IQueryable<EMPLOYEES> q1 = null;

foreach (var item in arr) { 
                            
    var temp_qry = (from emp in emp_qry
                    where emp.PAYROLL.StartsWith(item)
                    select emp);
    if (q1 == null)
        q1 = temp_qry;
    else
        q1 = q1.Union(temp_qry);
}

emp_qry = q1;