Ashwin Nair Ashwin Nair - 2 months ago 22
C# Question

How do I manipulate an NHibernate order by criteria projection?

I've got an NHibernate criterion

var criteria = GetCurrentSession().CreateCriteria<T>();


to which I add the following order by clauses:

var orderExpressions = new List<NHibernate.Criterion.Order>
{
NHibernate.Criterion.Order.Desc(Projections.Property<DT>(x => x.OrderDate)),
NHibernate.Criterion.Order.Asc(Projections.Property<DT>(x => x.Type))
};


using

foreach (var expression in orderExpressions)
{
criteria.AddOrder(expression);
}


Now this works and is equivalent to the following SQL statement:

select * from DT
order by order_date desc,
type asc


What I actually need is:

select * from DT
order by DATEADD(MINUTE, DATEDIFF(MINUTE, 0, order_date), 0) desc,
type asc


which is essentially ordering it based on the date but ignoring the seconds. How do I incorporate this to the above NHibernate criteria expression?

Answer

A bit of hunting around lead me to the SQLFunctionTemplate class for unsupported SQL functions. The query I required can be then made using:

var orderExpressions = new List<NHibernate.Criterion.Order>
{
    NHibernate.Criterion.Order.Desc(
        Projections.SqlFunction(
            new SQLFunctionTemplate(NHibernateUtil.DateTime, 
                "DateAdd(MINUTE, " + 
                new SQLFunctionTemplate(NHibernateUtil.DateTime,
                    "DateDiff(MINUTE, 0, ?1)"
                ) + 
                ", 0)"),
                NHibernateUtil.DateTime,
                Projections.Property<DocumentTracking>(x => x.OrderDate)
        )
    ),
    NHibernate.Criterion.Order.Asc(Projections.Property<DocumentTracking>(x => x.Type))
};

where

                new SQLFunctionTemplate(NHibernateUtil.DateTime,
                    "DateDiff(MINUTE, 0, ?1)"
                )

represents DATEDIFF(MINUTE, 0, order_date) (?1 represents a parameter to be given later)

and

         new SQLFunctionTemplate(NHibernateUtil.DateTime, 
            "DateAdd(MINUTE, " + 
            new SQLFunctionTemplate(NHibernateUtil.DateTime,
                "DateDiff(MINUTE, 0, ?1)"
            ) + 
            ", 0)")

represents the dateadd with the datediff. (this bit DATEADD(MINUTE, DATEDIFF(MINUTE, 0, order_date), 0)).

This bit lends the parameter (orderdate) for the datediff above:

                NHibernateUtil.DateTime,
                Projections.Property<DocumentTracking>(x => x.OrderDate)