Dynamic WHERE clause in LINQ

Question

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

I have dozens of flags in the form and pass them back: Dictionary <string, List <string →> (Dictionary <fieldName, List <values ​​→) in 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

alt text http://www.scottgu.com/blogposts/dynquery/step2.png

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

Read more on ScottGu's blog .

Answer 2

You can also use the PredicateBuilder from LinqKit to combine several types of lambda expressions using Or or And.

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

Answer 3

A simple approach might be if your columns are of a 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 4

I came up with a solution that even I can understand ... using the "Contains" method, you can combine as many WHERE as you want. If WHERE is an empty string, it is ignored (or evaluated as a selection of all). Here is my example of joining two tables in LINQ, applying multiple sentences, and populating a model class to return to the view. (this is the choice of 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

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

Here is a 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 6

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

Answer 7

You can use the Any () extension method. It 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 list objects.

Answer 8

This CodePlex project has what you want.

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

Project Description

Extends System.Linq.Dynamic to support the execution of Lambda expressions defined in the line with the Entity Framework or any provider that supports IQueryable.

Since this is an extension of the source code you can find on the Scott Guthrie Blog , this will allow you to do these things:

And things like this:

Answer 9

This is the solution I came across if anyone is interested.

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

First, we identify one type of element that we need to use (from TRow As DataRow), and then identify the "source" that we use and bind the identifier to this source ((source As TypedTableBase (Of TRow)). Must specify a predicate or the WHERE clause to be passed (the As Func (Of TRow, Boolean) predicate) that will either be returned as true or false. Then we determine how we want the returned information to be ordered (OrderByField As String). Then our function will return EnumerableRowCollection (Of TRow), our datarows collection that satisfies It matches the conditions of our predicate (EnumerableRowCollection (Of TRow). This is a basic example. Of course, you must make sure that your order field does not work contain zeros or handle this situation correctly and make sure that the column names (if you use a strongly typed data source, do not pay attention to it, it will rename the columns for you) are standard.