Sorting
This section discusses briefly the server-side sorting capabilities of the LLBLGen Pro runtime framework. The sorting discussed below is executed as an ORDER BY clause in the generated query.
Sorting
Sorting is the ability to order data in one or more fields ascending (A -> Z) or descending (Z -< A). You do this by constructing a SortExpression with one or more SortClauses. SortClauses are simple definitions which contain information about which field to sort and in which direction (ascending/descending).
QuerySpec extension methods
The QuerySpec query API offers a set of extension methods which make it easier to formulate sort clauses and sort expressions. To utilize these in your code, add the following line to the top of your code file:
using SD.LLBLGen.Pro.QuerySpec;
Imports SD.LLBLGen.Pro.QuerySpec
The examples in this section will use these extension methods. See for more examples of what
extension methods are available to you, the QuerySpec section,
or the LLBLGen Pro RTL Reference Manual, and check the extension methods on the classes EntityField
(SelfServicing) or EntityField2
.
// SQL: ORDER BY Country ASC
var sortExp1 = CustomerFields.Country.Ascending();
// SQL: ORDER BY CompanyName DESC
var sortExp2 = CustomerFields.CompanyName.Descending());
// specify them both to the .OrderBy Call:
var qf = new QueryFactory(); // query Factory
var q = qf.Customer
.OrderBy(sortExp1, sortExp2);
// fetch q here.
If you specify a sort clause or a set of sortclauses and a RelationCollection (which is almost always the case with a typed list) while you also specify that duplicates are not allowed, be sure the sort clauses are referring to fields in the result-set, otherwise the database can't obey the sort rule and will throw an exception, since all fields mentioned in an ORDER BY clause (which is the result of a sort clause) have to be in the resultset when a DISTINCT statement (the result of the specification that no duplicate rows have to be retrieved) is included.
When you want to sort on a field which has an aggregate function or an expression applied to it, be sure to specify the aggregate function or expression object to the field in the SortClause as well, with the same alias.
Case-insensitive sorting
On case-sensitive databases (default Oracle installations, Firebird etc.) it can be you want to sort alpha-numeric data case-insensitive. To achieve that, you should set the SortClause object's property CaseSensitiveCollation to true, identical to the FieldLikePredicate system for case-insensitive filtering.
Setting this property to true will make the query generator emit UPPER() around the field, thus UPPER(fieldname), or equivalent function for UPPER() on the particular database.
To make this easy, QuerySpec defines an extension method, CaseInsensitive, to easily set this property as shown in the following example:
var sorter = new SortExpression();
sorter.Add(CustomerFields.Country.Ascending());
sorter.Add(CustomerFields.CompanyName.Descending().CaseInsensitive());
Sorting on an expression
When a field with an expression is placed in a sort clause, it gives a dilemma: is this a field which is already in the projection, so an alias has to be emitted or is this an expression to use as the Order By clause?
By default, LLBLGen Pro Runtime Framework will choose the former, as it's the most common, and will emit the alias set on the field instead of the expression. To sort on an expression however, the property sortclause.EmitAliasForExpressionAggregateField has to be set to false (default: true).
You can use the sortclause.SetEmitAliasForExpressionAggregateField method to set this property and chain methods together.
Example, using a DbFunctionCall as an expression to sort on.
var orders = new EntityCollection<OrdersEntity>();
using(var adapter = new DataAccessAdapter())
{
// prepare the function call and the field to sort on
var datePartCall = new DbFunctionCall("CAST({0} AS DATE)", new object[] { OrdersFields.OrderDate });
ISortExpression sorter = new SortExpression(datePartCall.Descending()
.SetEmitAliasForExpressionAggregateField(false));
// fetch
adapter.FetchEntityCollection(orders, null, 0, sorter);
}