Recently we received a question from a customer about how to express a sort criteria based on a condition. As the sort expression in Genome is nothing else than an implicit function (lambda) executed on the candidate element to return the value to sort by, this can be quite easily achieved.
As a simple example, let’s use the Northwind database again. A customer there has a Country
and a Region
property. Imagine you want to order all customers in the USA by Region
and all the other customers by Country
. Additionally you want to have USA customers first, then the others.
A Genome set can be sorted using the OrderBy()
method. It takes an implicit function as a parameter, which is denoted using []
in OQL. The condition can be expressed using the ?
operator, which is translated to a CASE WHEN
in SQL. Hence, the order function can be expressed like this:
When the customer is located in the USA, then order by region; otherwise, order by country:
Country=="USA" ? Region : Country
To make sure customers located in the USA are listed first, I am prefixing the Region with a space (I know this is a hack, but it will do for this example). Hence, the final OQL looks like this:
extentof(Customer).OrderBy( [ Country == "USA" ? " " + Region : Country])
which translates to the following SQL:
SELECT … FROM Customers
ORDER BY
CASE WHEN (Country="USA")
THEN (" " + Region)
ELSE (Country)
END ASC
Posted by Chris
Technorati Tags: object relational, getting started