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. Not all possibilities are described, as the functionality is useful in a lot of different scenario's. 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.

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 specifying the aggregate function as the AggregateFunctionToApply for a given field object. When a field object (an IEntityField instance (selfservicing) or IEntityField2 instance (adapter)) 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 (selfservicing, however for adapter it works the same):


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. For simplicity, the aggregate function is applied directly on the field, not specified with the DefineField() overload which accepts an AggregateFunction. 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 (SqlServer 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	Not on	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)	Access	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)	Access
CountRow	Calculates the number of rows.	COUNT(*)
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)	Firebird	works on floating point fields (float / single / etc.) only
StDevDistinct	Calculates statistical standard deviation for the distinct values of field.	Oracle: STDDEV(DISTINCT field)	SqlServer, Access, Firebird	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)	Firebird	works on floating point fields (float / single / etc.) only
VarianceDistinct	Calculates statistical variance over the distinct values of field.	Oracle: VARIANCE(DISTINCT field)	SqlServer, Access, Firebird	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.

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. Expression objects can be re-used, which makes them ideal objects for caching in your application so you don't have to write a lot of code to perform common operations. 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. Keep in mind that with this functionality, you can write very powerful queries with very little code directly in C# or VB.NET so not every situation you are able to use expressions in is explained 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:

• Field
• Field ExOp Field
• Field ExOp Value
• Field ExOp Expression
• Value ExOp Field
• Value ExOp Expression
• Expression ExOp Field
• Expression ExOp Expression
• Expression ExOp Value

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 (IEntityField instance (SelfServicing) or IEntityField2 instance (Adapter)) 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:



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. For each operand we'll construct an expression object.



You can also use this functionalty 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)

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.



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.