Click here to Skip to main content
11,438,226 members (53,779 online)
Click here to Skip to main content

Combining expressions to dynamically append criteria in LINQ where clause

, 12 Apr 2015 CPOL
Rate this:
Please Sign up or sign in to vote.
This article shows how to combine LINQ expressions to dynamically append where clause criteria when building search functionality

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:

/// <summary>
/// Creates a lambda expression that represents a conditional OR operation
/// </summary>
/// <param name="left">An expression to set the left property of the binary expression</param>
/// <param name="right">An expression to set the right property of the binary expression</param>
/// <returns>A binary expression that has the node type property equal to OrElse, 
/// and the left and right properties set to the specified values</returns>
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:

/// <summary>
/// Creates a lambda expression that represents a conditional AND operation
/// </summary>
/// <param name="left">An expression to set the left property of the binary expression</param>
/// <param name="right">An expression to set the right property of the binary expression</param>
/// <returns>A binary expression that has the node type property equal to AndAlso, 
/// and the left and right properties set to the specified values</returns>
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:

/// <summary>
/// Returns true, if this view model has criteria to search against
/// </summary>        
public Boolean HasCriteria()
{
    //get the properties of this object
    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":

            //the initial case starts off with a left expression as null. If that's the case,
            //then give the short-circuit operator something to trigger on for the right expression
            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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

MatthewThomas
Software Developer (Senior)
United States United States
Matthew is a software developer currently living and working in San Antonio Texas. When not working on software, Matthew enjoys spending time with his wife, children, and dog, and growing various types of food for them in his backyard garden.
Follow on   LinkedIn

Comments and Discussions

 
QuestionGreat demonstration of building up expressions Pin
John Brett20-Apr-15 3:58
memberJohn Brett20-Apr-15 3:58 
GeneralMy vote of 5 Pin
Simon Gulliver17-Apr-15 0:55
memberSimon Gulliver17-Apr-15 0:55 
QuestionPresidentNumber [modified] Pin
Alexander Chernosvitov14-Apr-15 9:47
memberAlexander Chernosvitov14-Apr-15 9:47 
AnswerRe: PresidentNumber [modified] Pin
MatthewThomas14-Apr-15 16:43
memberMatthewThomas14-Apr-15 16:43 
Questionsearching for booleans Pin
MeladNL14-Apr-15 0:26
memberMeladNL14-Apr-15 0:26 
AnswerRe: searching for booleans Pin
MatthewThomas14-Apr-15 16:38
memberMatthewThomas14-Apr-15 16:38 
GeneralRe: searching for booleans Pin
MeladNL17-Apr-15 3:11
memberMeladNL17-Apr-15 3:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150506.1 | Last Updated 12 Apr 2015
Article Copyright 2015 by MatthewThomas
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid