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 lt string  List lt string gt  gt   Dictionary lt fieldName List lt values gt  gt   to my LINQ query   public IOrderedQueryable lt ProductDetail gt  GetProductList string productGroupName  string productTypeName  Dictionary lt string List lt string gt  gt  filterDictionary        var q   from c in db ProductDetail             where c ProductGroupName    productGroupName  amp  amp  c ProductTypeName    productTypeName                insert dynamic filter here             orderby c ProductTypeName             select c      return q

User · Answer

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   gt  el Element s     null  amp  amp  el Element s  Value Contains searchTerm                   select fieldsToReturn Select r   gt   r     product     el   el Element r                              Console WriteLine root ToString       Where  fieldsToSearch  and  fieldsToReturn  are both List objects

User · Answer

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

User · Answer

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                     amp  amp  a STATUSCODE Contains StatusCode                     amp  amp                      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

User · Answer

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   gt  r SurveyID    surveyId   if  fromDate    null        votes   votes Where r   gt  r VoteDate Value  gt   fromDate     if  toDate    null        votes   votes Where r   gt  r VoteDate Value  lt   toDate     votes   votes Take LimitRows  OrderByDescending r   gt  r VoteDate

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   gt  p ProductDetailID  gt  0   Example prop                  amp  amp   String IsNullOrEmpty iproductGroupName     true   p iproductGroupName Equals iproductGroupName       use ternary operator to make the condition dynamic                  amp  amp   ID    0    true     p ID    IDParam                     ToList

User · Answer

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

User · Answer

A simple Approach can be if your Columns are of Simple Type like String  public static IEnumerable lt MyObject gt  WhereQuery IEnumerable lt MyObject gt  source  string columnName  string propertyValue       return source Where m   gt    return m GetType   GetProperty columnName  GetValue m  null  ToString   StartsWith propertyValue

[c#] Dynamic WHERE clause in LINQ

Examples related to c#

Examples related to linq

Examples related to dynamic

Examples related to where-clause