Sorting two columns using NHibernate Criteria API

Question

I need to sort a result set by two columns, one of which is always NULL.

A simplified view of Database where the columns I wish to sort on look like :

ColumnA is from Table A

Column B, B1 is from table B

ColumnA ColumnB ColumnB1
U           NULL    NULL
P           NULL    NULL
L           NULL    NULL
NULL     U      NULL
NULL     Y      def

Column A and Column B are later mapped in Entities as Name and I wish to merge and sort the results in the following fashion. The resultant table indicates ORDER BY Name DESC

  Name  ColumnB1    
  Y           def   
  U           NULL  
  U           NULL  
  P           NULL  
  L       NULL  

I tried to invoke coalesce operator in select statement "order by" clause using NHibernate Criteria API.

Does the API support coalesce function? Reason being I tried the following snippet but it did not yield the desired results. In fact I could not see the coalesce operator in the resulting sql using NHibernate profiler.

var projectionList = Projections.ProjectionList();
projectionList.Add(Projections.Property("ColumnA"));
projectionList.Add(Projections.Property("ColumnB"));

var result = Projections.SqlFunction("coalesce", 
NHibernateUtil.String, projectionList);

I'd seen a lot of examples on which involves invoking SQL functions such as Year, Month etc but not with coalesce.

Any pointers on how to go about with it?

Answer 1

I made some leeway with this and got struck elsewhere. The problem is to do with the way coalesce has been invoked. A Projection List is not required. Refer http://stackoverflow.com/questions/3639158/prevent-nhibernate-from-aliasing-in-order-by for Diego's answer.