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.

Invincible

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.

Ratio

Working great although what does the Alias do in this case? – Invincible

It just enables you to reference the table inside of the Restrictions.Where call. You could also try Restrictions.Where<Item>(i => i.Status == Status.Returned) if you prefer, but I have had weird results with that version. – Ratio

Brilliant. I used this in order to sort a table that contained some empty strings for last names. Not enough room here, but essentially, do the reverse via Projections.Conditional and ThenBy(x -> x.LastName).Asc and you're off to the races. Now can use QueryOver and Skip/Take for paging. – Malherbe

Hi Andrew, I am facing kind of similar situation, user supplied sequence need to be implemented or order by. When use Projection list I get "AS" in order by and result in error. When use static .Orderby() many times it works but when used with in if() conditions, there is no order by in SQL. Any idea how to achieve this? – Woodall

what if we have multiple case like case when Status='Returned' then 1 case when Anotherfield = 1 then 'this' case when othertablefield > 2 then 9 else 0 end – Woodall