Dynamic Search Objects Part 2—Dynamic List Wrapper

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

About these ads