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.