Have Code, Will Travel

November 17, 2009

Dynamic Search Objects Part 4—Paging Through Records

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

In my last post, we revised our requirements to read:

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.

While this took us a step closer to our original requirements (see Part 1), we are still missing a few features–paging and sorting.  We’ll cover paging in this installment, and save sorting for next time.  So for now our requirements can be amended to add:

The results should show a page of x records at a time, where x can be selected by the client.  The client should also be able to request and receive a specific page of data by entering the desired page number.

Our framework is based on LLBLGen Pro’s powerful dynamic list functionality, which already supports paging, so adding this flexibility is very straightforward.  We already have a class that encapsulates our search criteria (the parameter or DTO class), so all we really need to is modify it with a few additional fields, then use those fields at the appropriate time in our query service.

Violating YAGNI

I’m going to violate YAGNI slightly by creating a base class for our DTO which will store and expose paging criteria.  Why?  Because I know for a fact that I’m going to be writing more than one of these dynamic searches, and I’d rather not re-create the same functionality in each implementation.

The only change to our DTO/parameter class (not shown) is that it now inherits from a base class:

public abstract class SearchParamBaseV1
    {
        private int _pageNumber = 0;
        private int _pageSize = 0;
        
        public void SetPaging(int pageSize, int pageNumber)
        {
            _pageNumber = pageNumber;
            _pageSize = pageSize;
        }

        public int PageNumber
        {
            get { return _pageNumber; }
        }

        public int PageSize
        {
            get { return _pageSize; }
        }
    }

Our DynamicListWrapper class only needed two changes: the ExcecuteQuery signature and the first database call.  The changed lines:

public SearchQueryResultV2 ExecuteQuery(int pageNumber, int pageSize)

adapter.FetchTypedList(_fields, dt, _bucket, 0, null, false, _groupBy, pageNumber, pageSize);

Our search Service class didn’t need to change much–the call to the execute method now passes the paging information from the DTO object:

return listManager.ExecuteQuery(param.PageNumber, param.PageSize);

I also fixed a bug in the service class that I came across when I implemented paging (glad I decided to make writing tests part of my process!).  I found an explanation of why specifying the type is necessary here.  The code in question before:

listManager.AddField(new EntityField2("EmployeeName",
                                      EmployeesFields.FirstName + " " +
                                      EmployeesFields.LastName), false);

. . . and after:

listManager.AddField(new EntityField2("EmployeeName",
                                      EmployeesFields.FirstName + " " +
                                      EmployeesFields.LastName, typeof(string)), false);

I’m also going to modify the SearchQueryResult class to include the paging information.  This is useful in scenarios where the consumer of the result (e.g.the view in MVC) needs this information but has no knowledge of the the DTO or its values.

public class SearchQueryResultV2
    {
        public int TotalRows { get; private set; }
        public DataTable Data { get; private set; }
        public int PageNumber { get; private set; }
        public int PageSize { get; private set; }

        public SearchQueryResultV2(DataTable data, int totalRows, int pageNumber, int pageSize)
        {
            if (data == null) throw new ArgumentNullException("data");
            Data = data;
            TotalRows = totalRows;
            PageSize = pageSize;
            PageNumber = pageNumber;
        }
    }

In my next post, we’ll address the dreaded topic of sorting our results (cue foreboding music).

Source Code for Part 4

Shout it

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

November 12, 2009

Dynamic Search Objects Part 2—Dynamic List Wrapper

Filed under: Dynamic SQL — Phil Sandler @ 9:52 pm
Tags:

In my last post, I demonstrated a concrete implementation of a dynamic query, not very far removed from the basic functionality that ships with LLBLGen Pro.  In this installment, we’ll create a convenience wrapper that will generally make creating dynamic lists easier, and further facilitate rapidly moving from a set of requirements for a search to a working implementation.

The wrapper is essentially a stateful class that combines the structures that make up a Dynamic List, and provides a convenient way to put those structures together and then execute the query.

This class will be responsible for:

  • Allowing fields to be created
  • Managing the GroupBy collection via field creation
  • Allowing relations to be added
  • Allowing filters to be added
  • Allowing sort expressions to be added (future post)
  • Add paging (future post)
  • Managing the state of all of the above structures before query execution
  • Executing the query and returning a SearchQueryResult

The exact details of the wrapper should be tailored to the overall requirements of your system.  For example, if none of your search results require any kind of aggregation, the GroupBy clause functionality isn’t necessary.

Note that the responsibility of defining fields, adding relations, creating aggregates, etc. are still the responsibility of the query implementation.  The core of the pattern is to have one query implementation per parameter implementation; there is no getting around having to build classes that have specific knowledge about the fields and entities involved in the query.  Our goal is to make each implementation as simple as possible.

Version 1 of our Dynamic List Wrapper:

public class DynamicListWrapperV1
    {
        private readonly RelationPredicateBucket _bucket = new RelationPredicateBucket();
        private ResultsetFields _fields;
        private GroupByCollection _groupBy;

        public void AddRelation(IRelation relation)
        {
            _bucket.Relations.Add(relation);
        }

        public IPredicateExpression Filter
        {
            get { return _bucket.PredicateExpression; }   
        }

        public void AddField(IEntityField2 fieldToAdd, bool addToGroupBy)
        {
            AddField(fieldToAdd, addToGroupBy, fieldToAdd.Name);
        }

        public void AddField(IEntityField2 fieldToAdd, bool addToGroupBy, string fieldName)
        {
            //if this is the first field, instantiate
            if (_fields == null) _fields = new ResultsetFields(1);
            else _fields.Expand(1);
            
            _fields.DefineField(fieldToAdd, _fields.Count – 1, fieldName);
            
            if (addToGroupBy)
            {
                if (_groupBy == null) _groupBy = new GroupByCollection();
                _groupBy.Add(fieldToAdd);
            }
        }

        public SearchQueryResult ExecuteQuery()
        {
            int dbCount;
            var dt = new DataTable();
            using (var adapter = new DataAccessAdapter())
            {
                //fetch the data table
                adapter.FetchTypedList(_fields, dt, _bucket, 0, null, false, _groupBy);
                //perform an additional query to get the total count of rows that matched
                dbCount = adapter.GetDbCount(_fields, _bucket, _groupBy, false);
            }

            return new SearchQueryResult(dt, dbCount);
        }
    }

Our updated Order Search Service:

public class OrderSearchServiceV2
    {
        public SearchQueryResult SearchOrders(OrderSearchParamV1 param)
        {
            var listManager = new DynamicListWrapperV1();

            //create the fields.  This is roughly the equivalent of the SELECT statement
            listManager.AddField(OrdersFields.OrderId, true);
            listManager.AddField(OrdersFields.OrderDate, true);
            listManager.AddField(OrdersFields.ShippedDate, true);
            listManager.AddField(CustomersFields.CompanyName, true);
            listManager.AddField(EmployeesFields.FirstName, true);
            listManager.AddField(EmployeesFields.LastName, true);

            listManager.AddField(new EntityField2("EmployeeName",
                                                  EmployeesFields.FirstName + " " +
                                                  EmployeesFields.LastName), false);

            var countField = OrderDetailsFields.ProductId;
            countField.AggregateFunctionToApply = AggregateFunction.Count;
            listManager.AddField(countField, false, "OrderDetailCount");
            
            //setup relations.  This is roughly the equivalent of the FROM and JOIN statements
            listManager.AddRelation(OrdersEntity.Relations.CustomersEntityUsingCustomerId);
            listManager.AddRelation(OrdersEntity.Relations.EmployeesEntityUsingEmployeeId);
            listManager.AddRelation(OrdersEntity.Relations.OrderDetailsEntityUsingOrderId);

            //filter by customerId if it was provided
            if (param.CustomerId != null)
            {
                listManager.Filter.Add(OrdersFields.CustomerId == param.CustomerId);
            }

            //filter by StartDate if it was provided
            if (param.StartDate.HasValue)
            {
                listManager.Filter.Add(OrdersFields.OrderDate >= param.StartDate);
            }

            //filter by EndDate if it was provided
            if (param.EndDate.HasValue)
            {
                listManager.Filter.Add(OrdersFields.OrderDate <= param.EndDate);
            }

            return listManager.ExecuteQuery();
        }
    }

This is a pretty good start, and is an improvement over what we put together in Part 1.  However, we’re still dealing with a pared-down set of requirements which really don’t necessitate a dynamic query.  In my next post, we’ll add back some of these requirements, modify our parameter object to support more flexible filtering, and create a helper class to simplify creating those filters.

Source Code for Part 2

Shout it kick it on DotNetKicks.com

Blog at WordPress.com.