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.

      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: December 11, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Market pressures to move more quickly and develop innovative applications are forcing organizations to rethink how they develop and release applications. The combination of public clouds and physical back-end infrastructures are a means to get applications out faster. However, these hybrid solutions complicate DevOps adoption, with application delivery pipelines that span across complex hybrid cloud and non-cloud environments. Check out this …

  • VMware vCloud® Government Service provided by Carpathia® is an enterprise-class hybrid cloud service that delivers the tried and tested VMware capabilities widely used by government organizations today, with the added security and compliance assurance of FedRAMP authorization. The hybrid cloud is becoming more and more prevalent – in fact, nearly three-fourths of large enterprises expect to have hybrid deployments by 2015, according to a recent Gartner analyst report. Learn about the benefits of …

Most Popular Programming Stories

More for Developers

RSS Feeds