NHibernate sorting (SQL as a second option)

Question

I'm using NHibernate as my ORM and I'm trying to sort some data. The data needs to be retrieved paged.

Two of the columns in my Request table are UrgencyID and CreateDate. UrgencyID is a FK to the Urgency table with static data: 1 = Low, 2 = Normal, 3 = High, 4 = Critical.

I need to order my Requests in the following manner. Critical Requests by CreateDate descending All other requests by CreateDate descending

So my list of Requests should always have Critical by CreateDate desc at the top and then all other Requests (disregarding UrgencyID) by CreateDate desc

Is it possible to perform this sort order in NHibernate (using the Criteria API)?

If not, how would I do this in SQL? In a stored procedure?

EDIT: Solution thanks to both @DanP and @Michael Pakhantsov

Using the this_ prefix in the sql string as this is the default NHibernate prefix for the primary table selection.

public class OperatorJobQueueOrder : Order
    {
        public OperatorJobQueueOrder() : base("", true) { }
        public override NHibernate.SqlCommand.SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery)
        {
            return new NHibernate.SqlCommand.SqlString("case this_.JobRequestUrgencyID when 4 then 4 else 0 end desc, this_.CreatedDate");
        }
    }

Answer 1

You may be able to create a custom sort order to handle this through the critiera api; see this question for an example implementation.