Have Code, Will Travel

November 16, 2009

Dynamic Search Objects Part 3—Filtering and the Predicate Helper

Filed under: Dynamic SQL — Phil Sandler @ 4:54 pm
Tags:

In my last post, we created a Dynamic List Wrapper to help facilitate generating dynamic SQL.  Our wrapper exposed the PredicateExpression property of LLBLGen’s RelationPredicateBucket class, which is essentially the mechanism by which a developer can add to the WHERE clause of the final query.  In this installment, we will create a helper class that will ease transforming our filter criteria into an LLBL Predicate Expression.

How Far Should We Take This?

I have implemented this pattern in a number of systems to date, and the widest range of difference between the implementations is, without question, the level of flexibility/granularity in the filtering system.  There are a lot of possible options that can really amp up the power and utility of your framework:

  • Support for all comparison operators: =, !=, >, <, <=, >=
  • Support for all string comparisons: Equal, StartsWith, EndsWith, Contains
  • Support for aggregate filters (HAVING clause)
  • Support for combining filters with AND or OR (“OrderDate > x AND OrderDate <= y” )
  • Support for subquery filters (“WHERE CustomerId IN (SELECT CustomerId FROM . . .)”)

At the same time, implementing all these features increases complexity and adds time to unit and system testing.  For example, if you implement the ability to filter on multiple values on a string field AND choose the string comparison used for each value, your parameter object will need to express both the value and the operator to use, otherwise the query implementation will have no way to interpret it (you might use a dictionary or more complex structure to accomplish this).  This will result in more code paths, and many more cases to test against known data to ensure that your code is working properly.

Once again, the overall requirements of your system should dictate how much of this flexibility really needs to be built.

Keeping it Simple (at Least for Now)

We don’t need to get that granular for this article, so we’re going to define a few conventions:

  • Multiple filter values specified within a list will use “OR“
  • All other filters will use “AND”
  • String comparisons will effectively be “Contains” comparisons (“LIKE ‘%"’ + [value] + ‘%’”)
  • Date comparisons supported will be “greater than or equal” and “less than or equal”
  • All other comparisons will use the Equals operator.

Before we get to the code, let’s add back some of the requirements that we pared down in Part 1.  We limited our requirements to:

Show all orders, optionally filtered by any combination of: a customer, occurring after start date, occurring before an end date.

Let’s modify that to:

Show all orders, filtering by an optional list of customers and an optional list of employees who took the order and within an optional date range.

Previous code examples have shown the customer filter to use the CustomerId field.  I’m going to change that slightly here to demonstrate the string comparison functionality, which really wouldn’t make a lot of sense with an ID field (which would likely use an exact match, as criteria would be picked from a list).  I’m going to switch this to filter against the CompanyName field, which a user might more realistically want to search using only part of the name.

The Code

Here is the code for the predicate helper.  Since our requirements are pretty simple, there is no reason to break this into smaller classes at this point:

internal class PredicateHelper
    {
        internal PredicateExpression CreateStringFilter(IList<string> values, IEntityField2 fieldToFilter)
        {
            var pred = new PredicateExpression();
            foreach(string value in values)
            {
                pred.AddWithOr(new FieldLikePredicate(fieldToFilter, null, "%" + value + "%"));
            }
            return pred;
        }

        internal IPredicate CreateIntFilter(List<int> values, EntityField2 fieldToFilter)
        {
            var pred = new PredicateExpression();
            foreach (int value in values)
            {
                pred.AddWithOr(new FieldCompareValuePredicate(fieldToFilter, null,
                    ComparisonOperator.Equal, value));
            }
            return pred;
        }

        internal PredicateExpression CreateDateGreaterEqualFilter(DateTime? value, IEntityField2 fieldToFilter)
        {
            var pred = new PredicateExpression();
            if (value.HasValue)
            {
                pred.Add(new FieldCompareValuePredicate(fieldToFilter, null,
                    ComparisonOperator.GreaterEqual, value));
            }
            return pred;
        }

        internal PredicateExpression CreateDateLessEqualFilter(DateTime? value, IEntityField2 fieldToFilter)
        {
            var pred = new PredicateExpression();
            if (value.HasValue)
            {
                pred.Add(new FieldCompareValuePredicate(fieldToFilter, null,
                    ComparisonOperator.LessEqual, value));
            }
            return pred;
        }
    }

The relevant snippet of the search service V3 (the full version is included in the source code link):

var predicateHelper = new PredicateHelper();
listManager.Filter.Add(predicateHelper.CreateStringFilter(param.CompanyName, CustomersFields.CompanyName));
listManager.Filter.Add(predicateHelper.CreateDateGreaterEqualFilter(param.StartDate, OrdersFields.OrderDate));
listManager.Filter.Add(predicateHelper.CreateDateLessEqualFilter(param.EndDate, OrdersFields.OrderDate));
listManager.Filter.Add(predicateHelper.CreateIntFilter(param.EmployeeId, OrdersFields.EmployeeId));

In my next post, we’ll add paging back to our requirements, and modify our framework to support it.

Source Code for Part 3

Shout it

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: