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.

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