Multiple search criteria and EF code first

I run into the infamous advanced user search requirement that every database application user requests. There is an entity/model that the user need to search based on a combination of several criteria. In my case it is a single view in a database which is exposed using a read-only entity object with all relevant fields that are needed to display in a list.

The user is able to search the records using one of the three criteria shown above. As for the user interface part of the app, it is a no-brainer since all that is required is a form post when he/she clicks search.

The really annoying part is when handling the postback action. The first thing that comes to mind is to have a series of if… else blocks to handle the different cases. That really sucks! The good thing is there is a really neat hack that you can apply if you are using EF code first as your data access layer.

By writing a simple Linq expression extension to handle the different cases. Here is how it works.

First create a static class in your project and add this function to it:

Code Snippet

1. public static class SearchExtension
2. {
3.     public static IQueryable<TSource> WhereIf<TSource>(this IQueryable<TSource> source, bool condition, Expression<Func<TSource, bool>> predicate)
4.     {
5.         if (condition)
6.         {
7.             return source.Where(predicate);
8.         }
9.         return source;
10.     }
11. }

 

Next use the above method inside your controllers by passing the value together with the condition if it should be included in the criteria or not like:

Code Snippet

1. public ActionResult Index(ChildrenFilter filter)
2. {
3.     var query = context.ChildInfoes.WhereIf(filter.FullName != null, c => c.FullName.Contains(filter.FullName))
4.         .WhereIf(filter.SexId != 0, c => c.SexId == filter.SexId)
5.         .WhereIf(filter.ProjectSiteId != 0, c => c.ProjectSiteId == filter.ProjectSiteId);
6.  
7.     ViewBag.Children = query.ToList();
8.  
9.     return View();
10. }

I added other features to display the search criteria by passing in the ViewBag but the real genius lies in the WhereIf extension method where by it adds the predicate if the condition variable evaluates to true. This will work for any number of filter criteria thanks to the method chaining feature of C#.

The result will be: