LLBLGen Pro - generated code - using GROUP BY and HAVING clauses, SelfServicing

Generated code - using GROUP BY and HAVING clauses, SelfServicing

Preface

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.

Using GroupByCollection and Having Clauses

Data in lists, be it a TypedView, a TypedList or 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 a TypedList's or TypedView's Fill() method. 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 an 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. Currently you can't apply aggregate functions in the designer, but you can in your code. The example below applies aggregates and expressions to the fields in a typed list 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. The fetch action of the TypedList is not done by calling Fill() but by using a more low level method as Fill() will re-create the fields collection, which is not what we want because we'd lose the expression and aggregate on the second field. You can derive a class from the TypedList class and override BuildResultset(), in which you apply the aggregate and expression objects. Then it is possible to call Fill().

C#
VB.NET

// C#
OrderTotalsTypedList orderTotals = new OrderTotalsTypedList();
IGroupByCollection groupByClause = new GroupByCollection();
// grab the fields collection. 
IEntityFields fields = orderTotals.BuildResultset();	
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);
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, orderTotals, 0, null, null, orderTotals.BuildRelationSet(), 
	true, groupByClause, null, 0, 0);

 ' VB.NET .NET 1.x
Dim orderTotals As New OrderTotalsTypedList()
' grab the fields collection. 
Dim fields As IEntityFields = orderTotals.BuildResultset()

' Expression for total price: ((unitprice * quantity) - ((unitprice * quantity) * discount) )
Dim productPriceExpression As IExpression = New Expression( _
	OrderDetailsFields.UnitPrice, ExOp.Mul, OrderDetailsFields.Quantity)
Dim discountExpression As IExpression = New Expression( _
	productPriceExpression, ExOp.Mul, OrderDetailsFields.Discount)
Dim totalPriceExpression As IExpression  = New Expression( _
	productPriceExpression, ExOp.Sub, discountExpression)
fields(1).ExpressionToApply = totalPriceExpression
fields(1).AggregateFunctionToApply = AggregateFunction.Sum

Dim groupByClause As IGroupByCollection = New GroupByCollection()
groupByClause.Add(fields(0))
Dim havingFilter As IPredicateExpression = New PredicateExpression()
havingFilter.Add(New FieldCompareValuePredicate(fields(1), Nothing, ComparisonOperator.GreaterThan, 1000.0F))
groupByClause.HavingClause = havingFilter
Dim dao As New TypedListDAO()
dao.GetMultiAsDataTable(fields, orderTotals, 0, Nothing, Nothing, orderTotals.BuildRelationSet(), _
	True, groupByClause, Nothing, 0, 0)

Which is equivalent to (VB.NET .NET 2.0)

Dim orderTotals As New OrderTotalsTypedList()
' grab the fields collection. 
Dim fields As IEntityFields = orderTotals.BuildResultset()
Dim groupByClause As IGroupByCollection = New GroupByCollection()
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)
Dim dao As New TypedListDAO()
dao.GetMultiAsDataTable(fields, orderTotals, 0, Nothing, Nothing, orderTotals.BuildRelationSet(), _
	True, groupByClause, Nothing, 0, 0)

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.