is it possible to have Different values with nhibernate?

Question

I have a person object that I am sorting:

 persCriteria = criteria.GetExecutableCriteria(Session)  
                .AddOrder(Order.Asc("LastName"))
                .AddOrder(Order.Asc("FirstName"));

I also have an attribute called CommonName.

What do i want? Well I want to be able to sort on CommonName if the person has a CommonName else sort on FirstName(first).

Now I'm wondering is it possible to indicate via NHibernate that it should orderby CommonName if the person has a CommonName else order by FirstName? And if so, how?

Answer 1

It is possible. We can use Conditional projection:

var conditionalOrderBy = Projections.Conditional
(
    Restrictions.IsNull("CommonName") // or LastName, not sure from a question snippet
  , Projections.Property("FirstName")
  , Projections.Property("CommonName") // or LastName
);

var list = criteria.GetExecutableCriteria(session)  
  .AddOrder(new Order(conditionalOrderBy, true))
  .List<Person>()
;

And SQL statement we get would be like:

...
ORDER BY (CASE when this_.CommonName IS NULL 
  THEN this_.FirstName 
  ELSE this_.CommonName END) 
 ASC