CODEWITHSUNDEEP
sqlnhibernatesorting
Ciaran O'Neill
Ciaran O'Neill

Reputation: 2634

NHibernate sorting (SQL as a second option)

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

Upvotes: 4

Views: 788

Answers (2)

DanP
DanP

Reputation: 6478

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

Upvotes: 2

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25370

 IN SQL ORDER BY will be

  ORDER by case UrgencyId when 4 then 4 else 0 end, CreateDate desc

Upvotes: 1

Related Questions