NHibernate Sort (SQL as Second Option)

Question

I am using NHibernate as my ORM and I am trying to sort some data. Data must be recovered.

The two columns in my query table are UrgencyID and CreateDate. UrgencyID is the FK for the Urgency table with static data: 1 = low, 2 = normal, 3 = high, 4 = critical.

I need to order my Requests as follows. Critical queries from CreateDate descending All other CreateDate queries descending

So my query list should always have Critical from CreateDate desc at the top and then all other queries (excluding UrgencyID) using CreateDate desc

Can this sort order be done in NHibernate (using the Criteria API)?

If not, how would this be done in SQL? In a stored procedure?

EDIT: solution thanks to @DanP and @Michael Pakhantsov

Use this prefix in the sql row, since this is the default NHibernate prefix to select the main table.

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

  IN SQL ORDER BY will be ORDER by case UrgencyId when 4 then 4 else 0 end, CreateDate desc 

Answer 2

You may be able to create your own sort order to handle this via critiera api; see this question for an example implementation.