Introduction
When implementing search functionality with LINQ, dynamically attaching an AND or OR clause inside your .Where method is not the most straightforward of things to do. This article presents a way to make that easy.
Background
You've had to build it, I've had to build it, we've all had to build it. It's built so much it's almost a developer's rite of passage. Build me a page (or form) that searches some data set, and give me specific text boxes so that I can search by <Insert list of fields>. Give me the option to match all or any of the criteria I specify, and if I provide no criteria, then return all search results.
Then we get to the code.
Provided first name and last name search criteria are provided, what we want to write is something that looks like this
var results = people.Where(p => p.FirstName.Like(FirstNameCriteria) ||
p.LastName.Like(LastNameCriteria));
But what if last name wasn't specified? What if there are ten other criteria? What if none of the criteria were specified? What if the entity has null for a first name instead of empty string? The query gets a lot harder then.
We could solve this by intersecting result sets for AND operations, and unioning result sets for OR operations, but then when our query executes, we end up with really bad performance as we're searching the data set multiple times when we should only be searching it once.
This is a problem I've had to solve multiple times now across multiple projects, and I think it's unique enough in its implementation that it's worth sharing.
The Code
This example is a lot easier to understand when there's some data behind it, so I went to make a list of all of the U.S. presidents, their time in office, and their political party based on information from this wikipedia article. This provides us with a varied data set, along with multiple properties to query by.
Additionally, I provided examples for ASP.NET MVC, WPF, and Web API. Web API is the simplest example, so that's what I'll be using for the purposes of this article. I'll leave it to you as an exercise to see how ASP.NET MVC and WPF are implemented.
Let's start off by building our extension methods - they are going to serve as the base for everything that follows.
public static class ObjectExtensions
{
public static String ToStringInstance(this Object obj)
{
if(obj == null)
{ return String.Empty;}
return obj.ToString();
}
}
public static class StringExtensions
{
public static Boolean HasValue(this String str)
{
return String.IsNullOrWhiteSpace(str) == false;
}
public static Boolean Like(this String str, String other)
{
return str.ToUpper().Contains(other.ToUpper());
}
}
When combined, our .ToStringInstance, and .HasValue methods will allow us the syntatic sugar we need to build a query that determines if any criteria was specified by the user.
We'll also need a way to separate search criteria properties from everything else in our search model (search operators, non-search criteria view model properties, etc.). Let's add an attribute that will let us signal that:
[AttributeUsage(AttributeTargets.Property, AllowMultiple=false)]
public class SearchCriteriaAttribute : Attribute
{}
The key to making this whole thing work is an ExpressionVisitor, which is new to .NET 4.0. Essentially, what we're doing here is using the ExpressionVisitor to allow the parameters for a given expression to flow from the left expression to the right expression.
For example, if we have expressions (foo) => foo == bar and (foo) => foo == baz, we want to use the ExpressionVisitor to generate an expression such that the value for foo used in the first expression is the same value used in the second.
public class ExpressionParameterReplacer : ExpressionVisitor
{
private IDictionary<ParameterExpression, ParameterExpression> ParameterReplacements { get; set; }
public ExpressionParameterReplacer
(IList<ParameterExpression> fromParameters, IList<ParameterExpression> toParameters)
{
ParameterReplacements = new Dictionary<ParameterExpression, ParameterExpression>();
for(int i = 0; i != fromParameters.Count && i != toParameters.Count; i++)
{ ParameterReplacements.Add(fromParameters[i], toParameters[i]); }
}
protected override Expression VisitParameter(ParameterExpression node)
{
ParameterExpression replacement;
if(ParameterReplacements.TryGetValue(node, out replacement))
{ node = replacement; }
return base.VisitParameter(node);
}
}
To create a wide search, we'll need a way to OR our expressions together:
public static Expression<Func<T, Boolean>>
OrElse(Expression<Func<T, Boolean>> left, Expression<Func<T, Boolean>> right)
{
Expression<Func<T, Boolean>> combined = Expression.Lambda<Func<T, Boolean>>(
Expression.OrElse(
left.Body,
new ExpressionParameterReplacer(right.Parameters, left.Parameters).Visit(right.Body)
), left.Parameters);
return combined;
}
What we do here is generate a whole new lambda expression which combines the left and right expressions.
If we were to say Expression.OrElse(left, right), that would be the equivalent of saying
(foo=> foo == bar) || (foo => foo == baz)
Instead, we'll construct a whole new lambda, and get the following:
foo => (foo == bar || foo == baz)
To create a more narrow search, we'll build AND expressions, and use very similar code:
public static Expression<Func<T, Boolean>>
AndAlso(Expression<Func<T, Boolean>> left, Expression<Func<T, Boolean>> right)
{
Expression<Func<T, Boolean>> combined = Expression.Lambda<Func<T, Boolean>>(
Expression.AndAlso(
left.Body,
new ExpressionParameterReplacer(right.Parameters, left.Parameters).Visit(right.Body)
), left.Parameters);
return combined;
}
Now that we have the ability to combine expressions, we need a search model. Let's start by adding all of the criteria we're going to allow the user to search for presidents by, as well as the search operator that the user specifies.
Our search operator is going to let the user specify whether or not to show results that match all criteria, or results that match any criteria. Values should be limited to "ANY" or "ALL".
public class SearchModel
{
[SearchCriteria]
public String FirstName { get; set; }
[SearchCriteria]
public String LastName { get; set; }
[SearchCriteria]
public String PresidentNumber { get; set; }
[SearchCriteria]
public Nullable<DateTime> StartDate { get; set; }
[SearchCriteria]
public Nullable<DateTime> EndDate { get; set; }
[SearchCriteria]
public String TermCount { get; set; }
[SearchCriteria]
public Nullable<Boolean> Alive { get; set; }
public String SearchOperator { get; set; }
}
You'll note that the criteria we allow the user to search on is decorated with the [SearchCriteria] attribute that we built earlier. This allows us to implement the following method on the search model:
public Boolean HasCriteria()
{
var properties = this.GetType().GetProperties(BindingFlags.Public |
BindingFlags.Instance | BindingFlags.FlattenHierarchy);
var searchProperties = properties.Where(p => p.CustomAttributes.Select
(a => a.AttributeType).Contains(typeof(SearchCriteriaAttribute)));
return searchProperties.Any(sp => sp.GetValue(this).ToStringInstance().HasValue());
}
Our HasCriteria method allows us to quickly tell whether or not we need to go through the hassle of injecting our where clause.
We'll also need a way to append our ANDs and OR clauses based on what the user specified, so let's build that too:
private Expression<Func<Model.President, Boolean>> AppendExpression
(Expression<Func<Model.President, Boolean>> left,
Expression<Func<Model.President, Boolean>> right)
{
Expression<Func<Model.President, Boolean>> result;
switch (SearchOperator)
{
case "ANY":
if (left == null)
{ left = model => false; }
result = ExpressionExtension<Model.President>.OrElse(left, right);
break;
case "ALL":
if (left == null)
{ left = model => true; }
result = ExpressionExtension<Model.President>.AndAlso(left, right);
break;
default:
throw new InvalidOperationException();
}
return result;
}
If you remember building truth tables, you'll know that starting with a false will let an OrElse continue evaluating, and starting with a true will let an AndAlso continue evaluating.
Additionally, you should note here that we're dealing with Expression<Func<T>>, not Func<T>. This is an important distinction to make because Func<T> is a pointer to a method implementation, while Expression<Func<T>> is the structure which describes how Func<T> is implemented.
If it helps to make an analogy, Expression<Func<T>> is like the raw source code, while Func<T> is its compiled binary equivalent.
So, by passing expressions as opposed to the delegate itself, we can manipulate the query prior to execution, so that when we do resolve the result set, we're limiting what comes across the wire to what the user actually wants.
Now it's time to actually build the expression that's going to serve as our where clause. Let's take a look at what that looks like:
public Expression<Func<Model.President, Boolean>> ToExpression()
{
Expression<Func<Model.President, Boolean>> result = null;
int presidentNumberIntValue = 0;
if(PresidentNumber.HasValue() && Int32.TryParse(PresidentNumber, out presidentNumberIntValue) && presidentNumberIntValue > 0)
{
Expression<Func<Model.President, Boolean>> expr = model => model.PresidentNumber == presidentNumberIntValue;
result = AppendExpression(result, expr);
}
if (FirstName.HasValue())
{
Expression<Func<Model.President, Boolean>> expr = model => model.FirstName.Like(FirstName);
result = AppendExpression(result, expr);
}
if (LastName.HasValue())
{
Expression<Func<Model.President, Boolean>> expr = model => model.LastName.Like(LastName);
result = AppendExpression(result, expr);
}
if (StartDate.HasValue)
{
Expression<Func<Model.President, Boolean>> expr = model => model.TookOffice >= StartDate;
result = AppendExpression(result, expr);
}
if (EndDate.HasValue)
{
Expression<Func<Model.President, Boolean>> expr = model => model.LeftOffice <= EndDate;
result = AppendExpression(result, expr);
}
if(Alive.HasValue)
{
Expression<Func<Model.President, Boolean>> expr = model => model.IsAlive == Alive;
result = AppendExpression(result, expr);
}
var termCounntIntValue = 0;
if (TermCount.HasValue() && Int32.TryParse
(TermCount, out termCounntIntValue) && termCounntIntValue > 0)
{
Expression<Func<Model.President, Boolean>> expr =
model => model.Terms.Count() == termCounntIntValue;
result = AppendExpression(result, expr);
}
return result;
}
As you can see, for every search criteria that the user specifies, we'll build an expression that searches the model for just that criteria, and then append it to our overall expression.
Let's jump over now to the PresidentsController, and build out the API.
public class PresidentsController : ApiController
{
public IHttpActionResult Get(String firstName ="",
String lastName = "",
String presidentNumber = "",
DateTime? startDate = null,
DateTime? endDate = null,
String termCount = "",
Boolean? IsAlive = null,
String searchOperator = "ANY")
{
var searchModel = new SearchModel
{
FirstName = firstName,
LastName = lastName,
PresidentNumber = presidentNumber,
StartDate = startDate,
EndDate = endDate,
TermCount = termCount,
Alive = IsAlive,
SearchOperator = searchOperator
};
var presidents = PresidentRepository.GetAllPresidents();
if (searchModel.HasCriteria())
{
presidents = presidents.Where(searchModel.ToExpression());
}
return Ok(presidents);
}
}
It's pretty simple - accept the criteria from the user to build the model, and then if anything was specified, then use our built expression to limit the results, otherwise, just return everything.
You can try this out by starting it up, and doing a search for:
 | | |
| Presidents whose first name is like 'George' | | One term presidents between 1800 and 1900 |
Or any other combinations of search terms you can think of.
Credits
History
- 2015-04-12: Initial publication
