CODEWITHSUNDEEP
c#nhibernate
Ash
Ash

Reputation: 6035

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?

Upvotes: 1

Views: 1088

Answers (1)

Ash
Ash

Reputation: 6035

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)

Upvotes: 1

Related Questions