Generated code - Field expressions and aggregates

Preface

It is sometimes necessary to use Aggregate functions (when a GroupByCollection is used) and / or expressions. This section describes both elements, aggregate functions and expressions, for the purposes they can be used in. This section illustrates the most common usage of the expression functionality and the aggregate functions. Not all aggregate functions are supported on all supported databases as not all functions have a native statement on those databases. Examples are given in code which is applicable for both SelfServicing and Adapter, unless stated otherwise.

The code in the examples are in the lower-level API syntaxis. QuerySpec offers extension methods to make the same code less verbose, and examples of these methods are given, where applicable.

Aggregate functions

Aggregate functions are the functions which can be used in combination with group by statements. Aggregate functions can be applied to a field, by setting the AggregateFunctionToApply property for a given field object to an AggregateFunction value. When a field object has an aggregate function applied to itself, the field will end up in the query as the field with the aggregate function applied to itself. Example:

// C#
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(CustomerFieldIndex.Country, 0, "Country");
fields.DefineField(CustomerFieldIndex.CustomerID, 1, "AmountCustomers");
fields[1].AggregateFunctionToApply = AggregateFunction.CountDistinct;

// QuerySpec offers extension methods per aggregate function:
fields[1].CountDistinct();
' VB.NET
Dim fields As New ResultsetFields(2)
fields.DefineField(CustomerFieldIndex.Country, 0, "Country")
fields.DefineField(CustomerFieldIndex.CustomerID, 1, "AmountCustomers")
fields(1).AggregateFunctionToApply = AggregateFunction.CountDistinct

' QuerySpec offers extension methods per aggregate function:
fields(1).CountDistinct()

This dynamic list (see for more information about dynamic lists these sections: for SelfServicing, for Adapter) is suitable for retrieving the number of customers per country. Because the aggregate function CountDistinct is applied to the second field in the column, the field's representation in the select list of the SELECT query will change.

Instead of the normal (SQL Server is used as an example) "[dbo].[Customer].[CustomerID] AS [CustomerID]"
it will become "COUNT(DISTINCT [dbo].[Customer].[CustomerID]) AS [AmountCustomers]".

If you then also specify a GroupByCollection which groups on the first field, Country, the data retrieved will contain per country the number of customers originating from that country. See the dynamic list sections for details about retrieving dynamic list data.
Supported aggregate functions
Below you'll find a table with all the aggregate functions currently supported by LLBLGen Pro. Not all of these functions are supported by all databases, Firebird doesn't support the StDev function for example. All aggregate functions use the field they're applied to as the argument of the function, except the Count aggregate function, which ignores the field it is applied to and will result in a COUNT(*). With field the IEntityField / IEntityField2 object is meant the aggregate function is applied to.

AggregateFunction value Description SQL Remarks
None No aggregate function applied (default) Just the fieldname, no wrapper function  
Avg Calculates the average value for the field. AVG(field) works on numeric fields (decimal / int / float / byte / etc.) only
AvgDistinct Calculates the average value for the distinct values for field. AVG(DISTINCT field) works on numeric fields (decimal / int / float / byte / etc.) only
Count Calculates the number of rows for field. COUNT(field)  
CountDistinct Calculates the number of rows with distinct values for field. COUNT(DISTINCT field)  
CountRow Calculates the number of rows. COUNT(*)  
CountBig Same as Count but now as 64bit value, if supported by target RDBMS. Otherwise same as Count COUNT_BIG(field) Only supported on SQL Server 2005+
CountBigDistinct Same as CountDistinct but now as 64bit value,, if supported by target RDBMS. Otherwise same as CountDistinct COUNT_BIG(DISTINCT field) Only supported on SQL Server 2005+
CountBigRow Same as CountRow but now as 64bit value, if supported by target RDBMS. Otherwise same as CountRow COUNT_BIG(*) Only supported on SQL Server 2005+
Max Calculates the max value for field. MAX(field) works on numeric fields (decimal / int / float / byte / etc.) only
Min Calculates the min value for field. MIN(field) works on numeric fields (decimal / int / float / byte / etc.) only
Sum Calculates the sum of all values of field. SUM(field) works on numeric fields (decimal / int / float / byte / etc.) only
SumDistinct Calculates the sum of all distinct values of field. SUM(DISTINCT field) works on numeric fields (decimal / int / float / byte / etc.) only
StDev Calculates statistical standard deviation for the values of field. SqlServer: STDEV(field)
Oracle: STDDEV(field)
Access: STDEV(field)		 works on floating point fields (float / single / etc.) only
StDevDistinct Calculates statistical standard deviation for the distinct values of field. Oracle: STDDEV(DISTINCT field) works on floating point fields (float / single / etc.) only
Variance Calculates statistical variance for the values of field. SqlServer: VAR(field)
Oracle: VARIANCE(field)
Access: VAR(field)		 works on floating point fields (float / single / etc.) only
VarianceDistinct Calculates statistical variance over the distinct values of field. Oracle: VARIANCE(DISTINCT field) works on floating point fields (float / single / etc.) only


When the field also has an expression object applied to it, the expression will be evaluated first and will become the parameter for the aggregate function, instead of the field.
Aggregate functions in scalar queries
You can also execute scalar queries, to retrieve a single value, for example the total sum of the prices of the products of a given order. The following example illustrates such a query, using the AggregateFunction.Sum aggregate to calculate the total price for the order 10254. It uses an expression to calculate the product price (quantity * unitprice) which is then aggregated with Sum. The field used in the scalar query is not important, as it is replaced by the expression in the actual query anyway.

   SelfServicing
  • C#
  • VB.NET
// C#
OrderDetailsCollection orderDetails = new OrderDetailsCollection();
decimal orderPrice = (decimal)orderDetails.GetScalar(OrderDetailsFieldIndex.OrderId, 
	(OrderDetailsFields.Quantity * OrderDetailsFields.UnitPrice), AggregateFunction.Sum, 
	(OrderDetailsFIelds.OrderId == 10254));
' VB.NET
Dim orderDetails As New OrderDetailsCollection()
Dim orderPrice As Decimal = CDec(orderDetails.GetScalar(OrderDetailsFieldIndex.OrderId, _
	(OrderDetailsFields.Quantity * OrderDetailsFields.UnitPrice), AggregateFunction.Sum, _
	(OrderDetailsFIelds.OrderId == 10254)))

   Adapter
  • C#
  • VB.NET
// C#
DataAccessAdapter adapter = new DataAccessAdapter();
decimal orderPrice = (decimal)adapter.GetScalar(OrderDetailsFields.OrderId, 
		(OrderDetailsFields.Quantity * OrderDetailsFields.UnitPrice), AggregateFunction.Sum, 
		(OrderDetailsFIelds.OrderId == 10254));
' VB.NET
Dim adapter As DataAccessAdapter = New DataAccessAdapter()
Dim orderPrice As Decimal = CDec(adapter.GetScalar(OrderDetailsFields.OrderId, _
		(OrderDetailsFields.Quantity * OrderDetailsFields.UnitPrice), AggregateFunction.Sum, _
		(OrderDetailsFIelds.OrderId == 10254)))

Expressions

Expressions are objects which specify a graph of nested actions which are executed in the database during a query. Expressions allow you to specify powerful operations on fields which would otherwise require a stored procedure or view or a lot of entity processing outside of the database.

The usage of Expression objects can be divided in three groups: expressions in select list, expressions in predicates and expressions in entity updates. Each group will be discussed in detail below. LLBLGen Pro offers you to use various elements in an expression: not only values and EntityField(2) objects, but also database function calls (DbFunctionCall objects, see: Generated code - Calling a database function) and scalar queries, see below: Scalar query expressions. So everywhere you see Expression or IExpression you can use a DbFunctionCall or ScalarQueryExpression as well.

The different types of expressions you can produce are:
All values are transformed into parameters before the query is executed to prevent SQL injection attacks. The Expression class offers for each expression type a constructor for easy creation of an Expression object. An expression is build up using LeftOperand operator RightOperand, and in the case of 'Field' the operator and RightOperand are not available/not defined. Each expression is surrounded by '()' for easy nesting and proper separation of actions. When you specify a Field object and the Field object has an Expression object applied to itself already, the Field will be replaced by the Expression applied to it in the Sql query. You can apply an Expression object to a field by setting the field's ExpressionToApply property to the Expression class instance. Fields in an expression can be any field in the set of fields in the query, so if you specify a RelationCollection with your query, all fields of the different entities referred by the relations in the RelationCollection are available for an expression. You can thus create multi-entity spanning expressions.

ExOp is the expression operator enum definition and contains the following values: for arithmetic operations: Add, Div, Mul, Sub and Mod (modulo). For logical operations: And and Or. For comparison operations: Equal, GreaterEqual, GreaterThan, LessEqual, LesserThan, NotEqual. For bitwise operations: BitwiseOr, BitwiseAnd and BitwiseXor. The bitwise operations are for SqlServer or PostgreSql only.
Expressions in select lists
Besides the GetScalar usage of expressions in select lists, as was illustrated in the previous paragraph about Aggregate functions, expressions are very useful in select lists of dynamic lists and other selects / data retrieval actions. The GroupBy / Having example found in the typed list / typed view section of this documentation (for Selfservicing, for Adapter), illustrates how to use expressions to calculate values in the select statement, when the query is executed, before the data is loaded into the object, in this case a DataTable. Generally speaking, when a field object has an expression applied to it, it will be replaced by the SQL representing the expression. The field itself is not added to the Expression's SQL. You'll use Expression objects with dynamically created lists most of the time, or with scalar queries.

A more simpler example of using an expression in a select list is given below. It's a simple query which contains the orderid, productid and the row total. The row total is the result of the expression OrderDetails.UnitPrice * OrderDetails.Quantity. We're after the following query:

 -- SQL
SELECT	
	OrderID,
	ProductID,
	(UnitPrice * Quantity) AS RowTotal
FROM	[Order Details]

This query can be generated and fetched using the following code:

   SelfServicing
  • C#
  • VB.NET
 // C#
ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(OrderDetailsFields.OrderID, 0);
fields.DefineField(OrderDetailsFields.ProductID, 1);
fields.DefineField(new EntityField("RowTotal", 
	(OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity)), 2);
DataTable results = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, results, 0, null, null, null, true, null, null, 0, 0);

// QuerySpec alternative
var qf = new QueryFactory();
var q = qf.Create()
	.Select(OrderDetailFields.OrderId, OrderDetailFields.ProductId,
		(OrderDetailFields.UnitPrice * OrderDetailFields.Quantity).As("RowTotal"));
var dao = new TypedListDAO();
var results = dao.FetchAsDataTable(q);
 ' VB.NET
Dim fields As New ResultsetFields(3)
fields.DefineField(OrderDetailsFields.OrderID, 0)
fields.DefineField(OrderDetailsFields.ProductID, 1)
fields.DefineField(New EntityField("RowTotal", _
	(OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity)), 2)
Dim results As New DataTable()
Dim dao As New TypedListDAO()
dao.GetMultiAsDataTable(fields, results, 0, Nothing, Nothing, Nothing, True, Nothing, Nothing, 0, 0)

' QuerySpec alternative
Dim qf As New QueryFactory()
Dim q = qf.Create() _
	.Select(OrderDetailFields.OrderId, OrderDetailFields.ProductId, _
		(OrderDetailFields.UnitPrice * OrderDetailFields.Quantity).As("RowTotal"))
Dim dao As New TypedListDAO()
Dim results = dao.FetchAsDataTable(q)

   Adapter
  • C#
  • VB.NET
// C#
ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(OrderDetailsFields.OrderID, 0);
fields.DefineField(OrderDetailsFields.ProductID, 1);
fields.DefineField(new EntityField2("RowTotal", 
	(OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity)), 2);
DataTable results = new DataTable();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
	adapter.FetchTypedList(fields, results, null);
}

// QuerySpec alternative
var qf = new QueryFactory();
var q = qf.Create()
	.Select(OrderDetailFields.OrderId, OrderDetailFields.ProductId,
		(OrderDetailFields.UnitPrice * OrderDetailFields.Quantity).As("RowTotal"));
var results = new DataTable();
using(var adapter = new DataAccessAdapter())
{
	adapter.FetchAsDataTable(q, results);
}
 ' VB.NET
Dim fields As New ResultsetFields(3)
fields.DefineField(OrderDetailsFields.OrderID, 0)
fields.DefineField(OrderDetailsFields.ProductID, 1)
fields.DefineField(New EntityField2("RowTotal", _
	(OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity)), 2)
Dim results As New DataTable()
Using adapter As New DataAccessAdapter()
	adapter.FetchTypedList(fields, results, Nothing)
End Using

' QuerySpec alternative
Dim qf As New QueryFactory()
Dim q = qf.Create() _
	.Select(OrderDetailFields.OrderId, OrderDetailFields.ProductId, _
		(OrderDetailFields.UnitPrice * OrderDetailFields.Quantity).As("RowTotal"))
Dim results As New DataTable()
Using adapter As New DataAccessAdapter()
	adapter.FetchAsDataTable(q, results)
End Using

The 3rd field in the resultsetfields object is defined using a constructor call to EntityField(2), which creates a new EntityField(2) object with the name specified and with the expression passed in, in this example the expression OrderDetails.UnitPrice * OrderDetails.Quantity.

After that, it's simply fetching a normal Dynamic List.
Expressions in predicates
Expressions in predicates offer a very flexible way of creating powerful filters. The first way of using expressions in filters is by using the FieldCompareExpressionPredicate class (for SelfServicing, for Adapter). It offers you a way to compare a field with another field, with an arithmetic operation on a field or a group of fields and a lot of variations on this. As Expressions can be applied to fields and will replace the field in the query, you can also apply an Expression object to the field which is compared to the expression, or to the field in any of the predicate classes. If you want to perform the following WHERE clause: WHERE (field1 + field2) > (field3 * field4), you can do this as follows: You need two expressions (one for each expression in the clause) and one predicate class, the FieldCompareExpressionPredicate class. The complete clause can be described as: left operand > right operand. 

// C#
IPredicate filter = ((MyEntityFields.Field1 + MyEntityFields.Field2) > 
	(MyEntityFields.Field3 * MyEntityFields.Field4));
' VB.NET
Dim filter As IPredicate = ((MyEntityFields.Field1 + MyEntityFields.Field2) > _
	MyEntityFields.Field3 * MyEntityFields.Field4))

You can also use this functionality with all the other predicate classes defined in LLBLGen Pro.
Expressions in entity updates
Updating entities directly in the database is one of the features LLBLGen Pro offers you. You can easily update sets of entities in one query without the necessity of fetching the entities first. With an Expression applied to a field, the update queries can be even more powerful. Say you are in a generous mood and you want to update the salary of all employees with 10%? With an expression this is done in a single query. You can of course add filters to limit the scope of the update if you want. The following example illustrates the 10% salary increase. It uses the technique discussed in the entity collection section of SelfServicing, or as option 3 in modifying an entity using Adapter)

   SelfServicing
  • C#
  • VB.NET
// C#
EmployeeEntity employee = new EmployeeEntity();
employee.Fields[(int)EmployeeFieldIndex.Salary].ExpressionToApply = 
	(EmployeeFields.Salary + (EmployeeFields.Salary * 0.01f));
EmployeeCollection employees = new EmployeeCollection();
// no filter is specified, everybody gets 10% extra, but you could of course
// specify a filter to limit the scope of the update.
employees.UpdateMulti(employee, null);
' VB.NET
Dim employee As New EmployeeEntity()
employee.Fields(CInt(EmployeeFieldIndex.Salary)).ExpressionToApply = _ 
	(EmployeeFields.Salary + (EmployeeFields.Salary * 0.01f))
Dim employees As New EmployeeCollection()
' no filter is specified, everybody gets 10% extra, but you could of course
' specify a filter to limit the scope of the update.
employees.UpdateMulti(employee, Nothing)

   Adapter
  • C#
  • VB.NET,
// C#
EmployeeEntity employee = new EmployeeEntity();
employee.Fields[(int)EmployeeFieldIndex.Salary].ExpressionToApply = 
	(EmployeeFields.Salary + (EmployeeFields.Salary * 0.01f));
DataAccessAdapter adapter = new DataAccessAdapter();
// no filter is specified, everybody gets 10% extra, but you could of course
// specify a filter to limit the scope of the update.
adapter.UpdateEntitiesDirectly(employee, null);
' VB.NET
Dim employee As New EmployeeEntity()
employee.Fields(CInt(EmployeeFieldIndex.Salary)).ExpressionToApply = _ 
	(EmployeeFields.Salary + (EmployeeFields.Salary * 0.01f))
Dim adapter As DataAccessAdapter = New DataAccessAdapter()
' no filter is specified, everybody gets 10% extra, but you could of course
' specify a filter to limit the scope of the update.
adapter.UpdateEntitiesDirectly(employee, Nothing)
Scalar query expressions
Sometimes it is necessary to use a so called scalar query as part of an Expression or a scalar query which could be used as an Expression. A scalar query is a select statement which returns a single value. An example of a scalar query is the NumerOfOrders value in the following simple query:

 -- SQL
SELECT CustomerID,
(
	SELECT COUNT(*) 
	FROM Orders
	WHERE CustomerID = Customers.CustomerID
) AS NumberOfOrders
FROM Customers

This query returns all CustomerID's and per CustomerID the number of orders for that customer. The select statement which fetches the NumberOfOrders is in fact a ScalarQueryExpression class in the select list: set the ExpressionToApply property of the second field in the ResultsetFields to an instance of the ScalarQueryExpression class and at the spot of the field, the scalar query expression is placed, similar to including a normal expression in a select list, as shown before in the paragraph about expressions in selectlists.

   SelfServicing
  • C#
  • VB.NET
 // C#
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(CustomerFields.CustomerID, 0);
fields.DefineField(new EntityField("NumberOfOrders", 
	new ScalarQueryExpression(OrderFields.OrderId.SetAggregateFunction(AggregateFunction.Count),
				(CustomerFields.CustomerId == OrderFields.CustomerId))), 1);
DataTable results = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, results, 0, null, null, null, true, null, null, 0, 0);

// QuerySpec alternative
var qf = new QueryFactory();
var q = qf.Create()
	.Select(CustomerFields.CustomerId,
		qf.Order.CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId)
			.CountRow()
			.As("NumberOfOrders"));
var dao = new TypedListDAO();
var results = dao.FetchAsDataTable(q);
 ' VB.NET
Dim fields As New ResultsetFields(2)
fields.DefineField(CustomerFields.CustomerID, 0)
fields.DefineField(New EntityField("NumberOfOrders", _
	New ScalarQueryExpression(OrderFields.OrderId.SetAggregateFunction(AggregateFunction.Count), _
		(CustomerFields.CustomerId = OrderFields.CustomerId))), 1)
Dim results As New DataTable()
Dim dao As New TypedListDAO()
dao.GetMultiAsDataTable(fields, results, 0, Nothing, Nothing, Nothing, True, Nothing, Nothing, 0, 0)

' QuerySpec alternative
Dim qf As New QueryFactory()
Dim q = qf.Create() _
	.Select(CustomerFields.CustomerId, _
		qf.Order.CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId) _
			.CountRow() _
			.As("NumberOfOrders"))
Dim dao As New TypedListDAO()
Dim results = dao.FetchAsDataTable(q)

   Adapter
  • C#
  • VB.NET
// C#
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(CustomerFields.CustomerID, 0);
fields.DefineField(new EntityField2("NumberOfOrders", 
	new ScalarQueryExpression(OrderFields.OrderId.SetAggregateFunction(AggregateFunction.Count),
				(CustomerFields.CustomerId == OrderFields.CustomerId))), 1);
DataTable results = new DataTable();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
	adapter.FetchTypedList(fields, results, null);
}

// QuerySpec alternative
var qf = new QueryFactory();
var q = qf.Create()
	.Select(CustomerFields.CustomerId,
		qf.Order.CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId)
			.CountRow()
			.As("NumberOfOrders"));
var results = new DataTable();
using(var adapter = new DataAccessAdapter())
{
	adapter.FetchAsDataTable(q, results);
}
 ' VB.NET
Dim fields As New ResultsetFields(2)
fields.DefineField(CustomerFields.CustomerID, 0)
fields.DefineField(New EntityField2("NumberOfOrders", _
	New ScalarQueryExpression(OrderFields.OrderId.SetAggregateFunction(AggregateFunction.Count), _
		(CustomerFields.CustomerId = OrderFields.CustomerId))), 1)
Dim results As New DataTable()
Dim adapter As New DataAccessAdapter()
adapter.FetchTypedList(fields, results, Nothing)

' QuerySpec alternative
Dim qf As New QueryFactory()
Dim q = qf.Create() _
	.Select(CustomerFields.CustomerId, _
		qf.Order.CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId) _
			.CountRow() _
			.As("NumberOfOrders"))
Dim results As New DataTable()
Using adapter As New DataAccessAdapter()
	adapter.FetchAsDataTable(q, results)
End Using

Similar to the Dynamic List fetch with an expression described earlier in this section, this routine too uses an EntityField(2) object with an expression, this time the ScalarQueryExpression object. A filter is defined to bind the subquery to the containing query, as well as the field to return by the ScalarQueryExpression, in this case OrderEntity.OrderId with the Count aggregate function applied to it.

ScalarQueryExpression has more overloads, which accept a relationcollection, sortexpression and also a GroupByCollection. A ScalarQueryExpression automatically performs a TOP 1 on the resultset, so you can specify a query which actually doesn't return a single value: the generated SQL will make sure there'll be just one value returned by the scalar query so the containing SELECT statement won't fail at runtime.
LLBLGen Pro v2.0.0.0 documentation. ©2002-2006 Solutions Design