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

Lissa

I had same question ( https://mcmap.net/q/281745/-user-defined-filter-for-linq ), and @tvanfosson told me about Dynamic Linq ( code.msdn.microsoft.com/csharpsamples ). – Konyn

Answer 1

_{(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.

Partible

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

Answer 2

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

Emission

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

brilliant Answer !! – Lester

Answer 3

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

Airplane

Answer 4

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

Hepta

My application is complaining that this can't be translated to SQL (I'm using Postgres provider). Perhaps your code is running in memory rather than on the db? This would lead to bad performance on large datasets. – Skit

Answer 5

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

Koster

This doesn't appear to be dynamic, or at least in the way I expected. Using this code: where (!useAnalysisID ? (true) : (a.ANALYSIS_ID == analysisID)) LINQ generated this SQL on the Oracle DB side: WHERE (((CASE WHEN (:p__linq__0 <> 1) THEN 1 WHEN ("Extent1"."ANALYSIS_ID" = :p__linq__1) THEN 1 WHEN ("Extent1"."ANALYSIS_ID" <> :p__linq__1) THEN 0 END) = 1) but I expected this part of the WHERE clause to be omitted because useAnalysisID = false. – Expert

Answer 6

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

Heathenize

possible to do other than string ? – Ragucci

Answer 7

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.

Lx

Answer 8

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.

Incongruous

Answer 9

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

Pyretotherapy

Both links are not completely valid. Scott Guthrie's Blog is missing the code and the System.Linq.Dyanmic is a 404 – Rawdin

Answer 10

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.

Serenata

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. – Gemmulation

I do apologize. I am new here. – Serenata