QueryOver API OrderBy using Case

Question

How to execute the following LINQ to NHibernate query using the QueryOver API. This retrieves a list of all Item records from the database and places Items with a Returned status at the end of the list. Status is an Enum that maps to nvarchar in the database.

var workList = session.Query<Item>() .OrderBy(i=> i.Status == Status.Returned ? 1 : 0) .ToList(); 

SQL equivalent

 SELECT * FROM Item ORDER BY case when Status='Returned' then 1 else 0 end 

Of course I tried

 var workList = session.QueryOver<Item>() .OrderBy(i => i.Status == Status.Returned ? 1 : 0).Asc .ToList(); 

But I get the following

InvalidOperationException: variable 'i' of type 'MyProject.Model.Entities.Item' refers to scope '', but this is not defined

I cannot use LINQ due to a problem with some other functions in this case.

Answer 1

You should be fine using Projections.Conditional here:

 Item itemAlias = null; var workList = session.QueryOver<Item>(() => itemAlias) .OrderBy(Projections.Conditional( Restrictions.Where(() => itemAlias.Status == Status.Returned), Projections.Constant(1), Projections.Constant(0))).Asc .List(); 

This is a bit detailed, but it has to do its job.