NHibernate. Order by function result

Question

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?

Answer 1

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>();