Using LINQ with Dynamic Where Clauses

Introduction

LINQ (Language INtegrated Query) provides a great query language which can be used to simplify database and object quering. LINQ queries are embedded within code with out the need to be contained within a string as with in-line SQL. While this approach does have many advantages, at first glance it appears to have one disadvantage. Unlike LINQ, SQL statements can be created dynamically using various string concatentation. This often useful for when it is necessary dynamically add to a Where Clause and/or the Sort Order. While performed differently, LINQ is capable of performing the same tasks without the need for string concatentation.

To get started, we will create a basic class and a generic list to be used for searching as shown below:

  class Book 
  { 
     public string Title { get; set; } 
     public string Description { get; set; } 
     public int Pages { get; set; } 
     public double Popularity { get; set; } 
  }
  
  List<Book> BookList = new List<Book>;

The Book class and the BookList provide us with a simple data source which we can be used to perform queries against the data. Next we can create the starting point of the LINQ query as shown below:

  var query = from b in BookList 
       select new { b.Title, b.Description, b.Pages, b.Popularity };

This query will serve as the base for the dynamic where clause to follow. Given this data you would think that the user could perform queries to search on any combination of those fields, so 4x4 = 16. While you could create all 16 combinations it is very ineffecient. To make it as efficient as possible, you should put each parameter within an if statement and add the filters only when necessary. To start with, it would be logical to perform a LIKE comparison on the Title and Description fields and to perform range filters over the Pages and Popularity. Shown below is the code necessary to perform the dynamic Title filter.

  if (TitleSearch != "") 
  { 
     query = query.Where(b => b.Title.Contains(TitleSearch)); 
  }

Walking through this code we first need to determine if we should search by title. Next we use the statement query = query.Where( ... which appends the where clause onto the initial query created above. The next code block shows a similar code snippet to append the other 3 filters.

  if (DescSearch != "") 
  { 
     query = query.Where(b => b.Description.Contains(DescSearch)); 
  } 
  
  if (PagesMinSearch != null) 
  { 
     query = query.Where(b => b.Pages >= PagesMinSearch && b.Pages <= PagesMaxSearch); 
  } 
  
  if (PopMinSearch != null) 
  { 
     query = query.Where(b => b.Popularity >= PopMinSearch && b.Popularity <= PopMaxSearch); 
  }

This code shows a similar filter for Description Search and 2 range filters for Pages and Popularity. Before we end, I should bring up that another very common filter in dynamic SQL is the Order By. Again the order by can also be created dynamically in LINQ. Logically, you would think that is possible to perform the order by on the Title, Number of Pages and Popularity. The code block below shows an example of allowing the user to sort either ascending or descending by any of the 3 fields.

  if (OrderByAsc) 
  { 
     switch (OrderByField) 
     { 
        case "Title": 
              query = query.OrderBy(b => b.Title); 
              break; 
        case "Pages": 
              query = query.OrderBy(b => b.Pages); 
              break; 
        case "Pop" : 
            query = query.OrderBy(b => b.Popularity); 
           break; 
     } 
  }
  else 
  { 
     switch (OrderByField) 
     { 
        case "Title": 
              query = query.OrderByDescending(b => b.Title); 
              break; 
        case "Pages": 
              query = query.OrderByDescending(b => b.Pages); 
              break; 
        case "Pop" : 
              query = query.OrderByDescending(b => b.Popularity); 
              break; 
     } 
  }

Similar to Where clauses above, we use the query = query.OrderBy to append the desired order by to the query.

Conclusion

In addition to where and order by clauses many other operations can be peformed dynamically in LINQ, such as Joins. Joins can be very useful especially when needed by dynamic filters. It is important to note that using this append method to make further complex queries will not cause the query to execute at each step. To execute the query you will need to perform a ToList, ToArray, ToDictionary operation, iterate through the results or any of several other methods which will execute the query. This has the advantage of allowing the LINQ to delay execution as long as possible to assemble all of the necessary filters and order bys. These techniques used above can also be used for creating LINQ to SQL, LINQ to Entities and LINQ to XML queries.



About the Author

Chris Bennett

Chris Bennett is a Manager with Crowe Horwath LLP in the Indianapolis office. He can be reached at chris.bennett@crowehorwath.com.

Comments

  • Thanks for a simple solution!

    Posted by Jason on 08/19/2014 08:26am

    I searched for a few hours for a solution to this and good Lord did I find multitudes of ridiculously complicated, bloated and unnecessarily complex solutions. This is a most elegant and easy approach. I used this in my own solution and fixed a bottleneck within 10 minutes. Many thanks!

    Reply
  • ePEBRn Wf qz hmO SknV GY

    Posted by qrfVqxfpCj on 02/02/2013 08:31am

    buy soma online soma the muscle relaxer - soma other muscle relaxers

    Reply
  • Can you OR the WHERE clause

    Posted by RayBrack on 02/17/2010 06:47am

    Good article however is it possible to OR the WHERE clauses?

    • Yes and No

      Posted by CBennett on 02/21/2010 05:03pm

      The short answer is, Yes, it can be done. However, LINQ does not provide a WhereOr type method. So to provide an OR you need to perform some tricks to get it accomplished. One technique to do this is to use a contains clause upon a list.

      • Nice!

        Posted by BigFudge on 03/30/2015 07:49am

        Excellent attack method for building in dynamic 'OR' s, breaking it down to what an 'OR' actually is! Strangely I would not have thought of this, so thank you for saving me the laborious search of different predicate methods. Regards BigFudge

        Reply
      Reply
    Reply
  • Very clean, very simple, very helpful

    Posted by satrapu on 02/16/2010 08:51am

    Thank you very much for this article - I was looking forward to see how can LINQ deal with dynamic queries.

    • Thanks

      Posted by CBennett on 02/16/2010 09:54am

      I'm glad you like the article. I hope you find it useful.

      Reply
    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: May 18, 2015 @ 1:00 p.m. ET / 10:00 a.m. PT While the idea of using facial and or gesture recognitions to create a modern, intuitive game seems attractive, some developers may want to leverage Unity 3D as a way to accelerate their development. There are many different ways in which Intel and Unity Technologies have been working together to helps speed the develop of games with the Intel® RealSense™ SDK (Software Developer Kit), so come hear from a panel of experts on what we've done …

  • There has been growing buzz about DevOps. DevOps is a methodology that unites the often separate functions of software development (Dev) and production and operations (Ops) into a single, integrated, and continuous process. DevOps is about breaking down the barriers between Dev and Ops. It leverages people, processes, and technology to stimulate collaboration and innovation across the entire software development and release process. Dev and Ops should always be part of an integrated process, but that's not …

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date