QueryOver API OrderBy using Case

Question

How can I perform the following LINQ to NHibernate query using the QueryOver API. This gets a list of all records of Item from the DB and places Items with the status "Returned" to the end of the list. The status is an Enum which is mapped to a nvarchar in the database.

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

The SQL equivalent is

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

I've of course 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' referenced from scope '', but it is not defined

I can't use LINQ because of an issue with some other functionality in this case.

Answer 1

You should be fine using Projections.Conditional here instead:

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

It's a little verbose but it should get the job done.