Filter using Expression Trees

Question

I need to query database and filter based on parameters passed into the function. I am passing two date parameters (used as a date range), a name, and a status parameters. All the parameters can have 'and' or 'or' conditions. Basically, I would like to build an linq expression based on which parameters are populated and pass it to Entity Framework to return a result set.

How can I do this with minimum 'if' statements? If you could be kind enough to provide an explanation with your example code, that would be awesome. I am trying to learn expression trees so an explanation would help.

At this point I don't have much code. That is why I have posted here. I can list the method signature. What exactly are you looking for?

public enum EmployeeStatus
{
    FullTime,
    PartTime,
    Contract
}

public IEnumerable<Employee> FilterEmployees(DateTime? startDate, 
    DateTime? endDate, string employeeName, EmployeeStatus employeeStatus)
{   }

score 4 · Accepted Answer · answered Nov 08 '12 at 15:53

4

public IQueryable<Employee> FilterEmployees(IQueryable<Employee> query, DateTime? startDate, DateTime? endDate, string employeeName, EmployeeStatus employeeStatus)
{
    if (startDate != null)
        query = query.Where(x => x.StartDate >= startDate);

    // etc...

    return query;
}

answered Nov 08 '12 at 15:53

user1793714

329
2
3

Cool, this works. So, when do we actually use an expression tree? – Skadoosh Nov 08 '12 at 16:53

Aaron Newton · Answer 2 · 2012-11-09T02:42:08.453

All the parameters can have 'and' or 'or' conditions. - you could consider using the PredicateBuilder. See http://www.albahari.com/nutshell/predicatebuilder.aspx. Why? Because this allows you to write a single query, but add an AND/OR predicate only if it is needed. You may or may not require this feature, but it is a good feature to be aware of. There is no database overhead until the query is actually called - it provides a means to conditionally build an IQueryable where you may not want to match against fields under certain conditions. E.g. I used this the other day to ignore a product code field - with min-length 10 - on searches where the input string was less than 10 characters.

This will allow you to add AND/OR statements using an if-condition like so:

public IQueryable<Employee> FilterEmployees(IQueryable<Employee> query, DateTime startDate, DateTime endDate, string employeeName, EmployeeStatus employeeStatus)
{
    var predicate = PredicateBuilder.True<Employee>();

    //All names starting with 'A'
    predicate = predicate.And(x => x.Name.StartsWith("A"));

    //Add a condition only if the employee is PartTime
    if (employeeStatus == EmployeeStatus.PartTime)
    {
        //Add condition for when they start
        predicate = predicate.And(x => x.StartDate >= startDate);
    }
    else
    {
        //Say we don't care about the start date for the other employee statuses,
        //but we want to add condition for when non-part-time employees are due to leave
        predicate = predicate.And(x => x.EndDate <= endDate);
        //or their name ends in 'z'
        predicate = predicate.Or(x => x.Name.EndsWith("z"));
    }

    IQueryable<Employee> employees = query.FindBy(predicate); //you should probably use a repository here to return your query

    return employees

}

Note - this is intended as psuedo-code to demonstrate and may have errors - see the above link for the proper implementation.

Cool reply. I wanted to not have any 'if' statements in the code, but I am realizing that this might not be possible. There is no way of knowing which variables are populated with a check. — Skadoosh, Nov 09 '12 at 15:04
No problem. One other thing I should have mentioned - you can do something like a 'CASE WHEN' in fluent like so: `query.OrderBy(x => (x.ParentID.HasValue) ? x.ParentID : x.ID)`. This is basically a ternary if inside the lambda expression: see http://msdn.microsoft.com/en-us/library/ty67wk28(v=vs.80).aspx, http://msdn.microsoft.com/en-us/library/ms181765.aspx and http://msdn.microsoft.com/en-us/library/bb397687.aspx — Aaron Newton, Nov 10 '12 at 05:30
I tried to create an example of where you might use this (a case in an order-by): http://sqlfiddle.com/#!6/804e6/10. I wanted to get a simple parent/child sort working, but I always seem to need a preliminary sort (e.g. on `type`), but you get the idea. I might post my own question on how to get this working. — Aaron Newton, Nov 10 '12 at 05:34
There is a related discussion here: http://stackoverflow.com/questions/12976118/performing-a-parent-then-child-sort-in-linq — Aaron Newton, Nov 10 '12 at 05:43

Filter using Expression Trees

2 Answers2