CODEWITHSUNDEEP
nhibernatesql-order-by
Sergey Metlov
Sergey Metlov

Reputation: 26291

NHibernate. Order by function result

I need NHibernate to perform such query: 

SELECT * 
FROM Users
ORDER BY Func(FirstName, LastName)

Standart NHibernate Order class doesn't allow to do this. Is there any another way?
The fact is, named queries and HQL don't fit as a solution.

EDIT: I found, that it is possible to do on SQL: 

SELECT *, Func(FirstName, LastName) AS FullName 
FROM Users
ORDER BY FullName

So, maybe it is possible to add extra field to criteria (or may be projection), like in this example?

Upvotes: 6

Views: 22749

Answers (1)

Phill
Phill

Reputation: 18796

First of all, you don't need to create a 'full name', to order by two columns. You can do it like:

SELECT * 
FROM Users
ORDER BY FirstName, LastName

-Edit

Or if you want to define the ordering you can include 'asc/desc' after each column:

SELECT * 
FROM Users
ORDER BY FirstName DESC, LastName ASC

-End Edit

As for ordering in NHibernate, if you're using QueryOver, you can do:

var result = Session.QueryOver<Users>()
                    .OrderBy(x => x.FirstName).Desc
                    .ThenBy(x => x.LastName).Desc
                    .List();

As for Criteria, I think (not 100% sure) you can do:

var result = Session.CreateCriteria(typeof(Users))
                    .AddOrder(Order.Desc("FirstName"))
                    .AddOrder(Order.Desc("LastName"))
                    .List<Users>();

Upvotes: 14

Related Questions