By Keith Barrows


2009-05-11 14:34:42 8 Comments

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;
}

10 comments

@Thomas Stock 2009-05-11 14:39:31

alt text
(source: scottgu.com)

You need something like this? Use the Linq Dynamic Query Library (download includes examples).

Check out ScottGu's blog for more examples.

@Ryan Gates 2016-03-31 21:29:29

There is a ported version on github (github.com/kahanu/System.Linq.Dynamic), which I contribute to and help manage.

@Josué Camacho 2019-02-22 00:15:52

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();

@KJM 2017-12-17 18:53:57

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.

@FelixSFD 2017-12-17 18:56:05

A link to a solution is welcome, but please ensure your answer is useful without it: add context around the link so your fellow users will have some idea what it is and why it’s there, then quote the most relevant part of the page you're linking to in case the target page is unavailable. Answers that are little more than a link may be deleted.

@KJM 2017-12-17 18:59:28

I do apologize. I am new here.

@Nitin Bourai 2013-03-20 09:34:57

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); });
}

@Xavier John 2014-07-30 00:28:33

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);

@user6121177 2017-08-24 13:56:01

Best suited for my need and easy to use. Thank you.

@Sushant Yelpale 2019-11-07 05:26:37

brilliant Answer !!

@mike 2014-02-28 22:51:35

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);
    }

@khalil 2019-10-07 05:40:01

possible to do other than string ?

@Zignd 2013-09-21 20:03:57

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:

enter image description here

And things like this:

enter image description here

@Todd DeLand 2013-08-27 17:52:44

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.

@Linus 2009-05-13 01:08:24

You can also use the PredicateBuilder from LinqKit to chain multiple typesafe lambda expressions using Or or And.

http://www.albahari.com/nutshell/predicatebuilder.aspx

@TcKs 2009-05-11 14:40:15

I had same question ( User defined filter for linq ), and @tvanfosson told me about Dynamic Linq ( http://code.msdn.microsoft.com/csharpsamples ).

Related Questions

Sponsored Content

34 Answered Questions

[SOLVED] What is the Java equivalent for LINQ?

  • 2009-08-01 18:53:26
  • Ahmed
  • 268571 View
  • 805 Score
  • 34 Answer
  • Tags:   java linq

23 Answered Questions

[SOLVED] LINQ query on a DataTable

26 Answered Questions

[SOLVED] Deserialize JSON into C# dynamic object?

13 Answered Questions

[SOLVED] When to use .First and when to use .FirstOrDefault with LINQ?

  • 2009-06-21 19:15:29
  • Metro Smurf
  • 538257 View
  • 796 Score
  • 13 Answer
  • Tags:   c# .net linq

7 Answered Questions

[SOLVED] Multiple "order by" in LINQ

  • 2008-11-18 13:34:11
  • Sasha
  • 575710 View
  • 1535 Score
  • 7 Answer
  • Tags:   linq sql-order-by

9 Answered Questions

[SOLVED] Group by in LINQ

  • 2011-09-06 19:44:20
  • test123
  • 1217804 View
  • 1003 Score
  • 9 Answer
  • Tags:   c# linq group-by

17 Answered Questions

[SOLVED] SQL join: where clause vs. on clause

13 Answered Questions

[SOLVED] Conditional Linq Queries

19 Answered Questions

[SOLVED] Dynamic LINQ OrderBy on IEnumerable<T> / IQueryable<T>

3 Answered Questions

[SOLVED] How to handle null values in LINQ with multiple where clauses

Sponsored Content