Field expressions and aggregates

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 QuerySpec extension method syntax.

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:

var fields = new ResultsetFields(2);
fields.DefineField(CustomerFieldIndex.Country, 0, "Country");
fields.DefineField(CustomerFieldIndex.CustomerID, 1, "AmountCustomers");
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.

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.

var adapter = new DataAccessAdapter();
decimal orderPrice = (decimal)adapter.GetScalar(OrderDetailsFields.OrderId, 
        OrderDetailsFields.Quantity.Mul(OrderDetailsFields.UnitPrice), AggregateFunction.Sum, 
        OrderDetailsFIelds.OrderId.Equal(10254));

var orderDetails = new OrderDetailsCollection();
decimal orderPrice = (decimal)orderDetails.GetScalar(OrderDetailsFieldIndex.OrderId, 
    OrderDetailsFields.Quantity.Mul(OrderDetailsFields.UnitPrice), AggregateFunction.Sum, 
    OrderDetailsFIelds.OrderId.Mul(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 a couple of groups: expressions in select list, expressions in predicates, expressions in entity inserts 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.

Everywhere you see Expression or IExpression you can use a DbFunctionCall or ScalarQueryExpression as well.

Theory

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 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.

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.

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.

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:

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

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

var qf = new QueryFactory();
var q = qf.Create()
            .Select(OrderDetailFields.OrderId, OrderDetailFields.ProductId,
                (OrderDetailFields.UnitPrice.Mul(OrderDetailFields.Quantity)).As("RowTotal"));
var results = new DataTable();
using(var adapter = new DataAccessAdapter())
{
    adapter.FetchAsDataTable(q, results);
}

var qf = new QueryFactory();
var q = qf.Create()
            .Select(OrderDetailFields.OrderId, OrderDetailFields.ProductId,
                (OrderDetailFields.UnitPrice.Mul(OrderDetailFields.Quantity)).As("RowTotal"));
var dao = new TypedListDAO();
var results = dao.FetchAsDataTable(q);

Expressions in predicates

Expressions in predicates offer a very flexible way of creating powerful filters. If you want to perform the following WHERE clause: WHERE (field1 + field2) > (field3 * field4), you can do this as follows:

IPredicate filter = (MyEntityFields.Field1.Add(MyEntityFields.Field2))
                    .GreaterThan(MyEntityFields.Field3.Mul(MyEntityFields.Field4));

Expressions in entity inserts

When an entity is saved and it is a new entity, an insert query is issued. By default the entity fields' values are used for the insert, however you can also execute an expression as the value of the field to insert, e.g. a function call or a scalar query expression. To do so, set the ExpressionToApply property of the entity field in the entity to an Expression object.

There are a couple of restrictions for this:

• If the field with the expression is a PK, the expression is ignored. The main reason is that the value the field is set to isn't retrievable and therefore can't be synced with the pk in the entity.
• If a field is a sequenced field, the expression is ignored, sequences always take precedence.
• After a successful insert, prior to an optional refetch, the expressions are removed temporarily from the fields. This is done to make sure a refetch of the entity is successful, as the field now has a value due to the insert. After the refetch, the expressions are reassigned to the fields. 

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)

var employee = new EmployeeEntity();
employee.Fields[(int)EmployeeFieldIndex.Salary].ExpressionToApply = 
        EmployeeFields.Salary.Add(EmployeeFields.Salary.Mul(0.01f));
using(var 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);
}

var employee = new EmployeeEntity();
employee.Fields[(int)EmployeeFieldIndex.Salary].ExpressionToApply = 
        EmployeeFields.Salary.Add(EmployeeFields.Salary.Mul(0.01f));
var 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);

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:

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.

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);
}

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);