Using GROUP BY and HAVING clauses

This section discusses the usage of GROUP BY and HAVING clauses with typed lists, typed views and dynamic lists. Usage is the same for all three. This section is mainly meant for users who have to work with code bases which use the low-level API. Group by and Having clauses can also be specified in QuerySpec queries. Linq queries do allow group by clauses but Linq by definition doesn't support Having clauses so our implementation doesn't either.

Using GroupByCollection and Having Clauses

Data in lists, be it a TypedView, a TypedList or a Dynamic List, is often grouped into smaller sets, which are then processed by Aggregate functions. LLBLGen Pro allows you to specify a GroupByCollection when calling DataAccessAdapter.FetchTypedList() or DataAccessAdapter.FetchTypedView().

The GroupByCollection can contain a custom filter which will be used as a HAVING clause in the query to generate. The filter is a normal PredicateExpression and can contain any predicate you would otherwise use in a normal filter, with one restriction: fields referred to in a Having clause have to be part of the GroupByCollection or have to have an aggregate function applied to them. This is a SQL restriction. For more information about aggregate functions, please see Field expressions and aggregates.

To make effective use of a GROUP BY action, fields in the resultset should be in the GroupByCollection or have an aggregate function applied to them. You can apply aggregate functions in the TypedList editor, and also in your code. The example below applies aggregates and expressions to the fields in a TypedList called OrderTotals. The TypedList contains just two fields: OrderDetails.OrderId (aliased as "OrderId" at index 0) and OrderDetails.UnitPrice (aliased as "TotalPrice", as it will contain the total price of the order, at index 1).

By applying expressions, aggregates and a group by action, together with a having clause, the typed list will contain all orders with a total price higher than $1,000.=. The data also could have been read using a dynamic list, as is illustrated later in this section. By assigning the expression and aggregate function to the field object which is used in both the fields list for fetching and the having clause, the expression and aggregate are used in both the select list and the having clause.

OrderTotalsTypedList orderTotals = new OrderTotalsTypedList();
IGroupByCollection groupByClause = new GroupByCollection();
// grab the fields collection. 
IEntityFields2 fields = orderTotals.GetFieldsInfo();    
groupByClause.Add(fields[0]);
// construct Having filter. 
// Expression for total price: ((unitprice * quantity) - ((unitprice * quantity) * discount) )
groupByClause.HavingClause = new PredicateExpression(
    fields[1]
        .SetExpression(
            (OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity) - 
            ((OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity) * OrderDetailsFields.Discount))
        .SetAggregateFunction(AggregateFunction.Sum)
    > 1000.0f);
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, orderTotals, orderTotals.GetRelationInfo(), 0, null, true, groupByClause);
}

Dim orderTotals As New OrderTotalsTypedList()
Dim groupByClause As IGroupByCollection = New GroupByCollection()
' grab the fields collection. 
Dim fields As IEntityFields2 = orderTotals.GetFieldsInfo()
groupByClause.Add(fields(0))
' construct Having filter. 
' Expression for total price: ((unitprice * quantity) - ((unitprice * quantity) * discount) )
groupByClause.HavingClause = New PredicateExpression( _
    fields(1) _
        .SetExpression( _
            (OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity) - _
            ((OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity) * OrderDetailsFields.Discount)) _
        .SetAggregateFunction(AggregateFunction.Sum) _
    > 1000.0)
Using adapter As New DataAccessAdapter()
    adapter.FetchTypedList(fields, orderTotals, orderTotals.GetRelationInfo(), 0, Nothing, True, groupByClause)
End Using

The main part is the creation of the Expression object which will calculate the proper total for an order. As expression objects are re-usable objects, the code might look a little verbose, but can be re-used in your application. The GroupByCollection's HavingClause is set to a FieldCompareValuePredicate object which compares the TotalPrice value with a value of 1000.0. As the field used in the predicate is the same as the field in the resultset, we get the proper expression and aggregate function applied to the field in the Having clause, as the Total price has to be re-calculated in the Having clause.