NHibernate排序(SQL作为第二种选择) [英] NHibernate sorting (SQL as a second option)

问题描述

我正在使用NHibernate作为我的ORM，并且正在尝试对一些数据进行排序.需要分页检索数据.

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

我的请求表中的两列是UrgencyID和CreateDate. UrgencyID是带有静态数据的Urgency表的FK: 1 =低，2 =正常，3 =高，4 =严重.

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.

我需要以以下方式订购我的请求. CreateDate的关键请求降序 由CreateDate降序

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

因此，我的请求列表应始终在CreateDate desc的顶部始终显示Critical，然后在所有其他Requests(不考虑UrgencyID)的CreateDate desc顶部显示

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

是否可以在NHibernate中执行这种排序顺序(使用Criteria API)?

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

如果没有，我将如何在SQL中执行此操作?在存储过程中?

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

感谢@DanP和@Michael Pakhantsov

在sql字符串中使用this_前缀，因为这是主表选择的默认NHibernate前缀.

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

推荐答案

 IN SQL ORDER BY will be

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

这篇关于NHibernate排序(SQL作为第二种选择)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！