Order by null/not null with ICriteria

Question

I'd like to sort my result like this:

• First I want all rows/objects where a column/property is not null, then all where the colmn/property is null.
• Then I want to sort by another column/property.

How can I do this with ICriteria? Do I have to create my own Order class, or can it be done with existing code?

ICriteria criteria = Session.CreateCriteria<MyClass>()
  .AddOrder(Order.Desc("NullableProperty")) // What do I do here? IProjection? Custom Order class?
  .AddOrder(Order.Asc("OtherProperty"));

I'd like to have an order like this:

NullableProperty  OtherProperty
----------------  -------------
1                 2
8                 7
5                 9
NULL              1
NULL              3
NULL              8

Cyr

Answer 1

I finally have a working answer for this. I didn't think it was possible before (10k's can see my deleted answer), but I started with a SQL query:

SELECT Id, NullableProperty, OtherProperty
    FROM NullableSorting
    ORDER BY
        (CASE WHEN NullableProperty IS NULL THEN 1 ELSE 0 END),
        OtherProperty

and then converted it to using the criteria interfaces. All the objects used here are built-in.

ICriteria criteria =
    session.CreateCriteria(typeof(NullableEntity))
    .AddOrder
    (
        Order.Asc
        (
            Projections.Conditional
            (
                Restrictions.IsNull("NullableProperty"),
                Projections.Constant(1),
                Projections.Constant(0)
            )
        )
    )
    .AddOrder(Order.Asc("OtherProperty"));

Trophy

In my case I wanted nulls last, but still ordering by NullableProperty for the non-null values. However, this is a great answer that got me 99% of the way there. For anyone else that wants the same, just add in .AddOrder(Order.Asc("NullableProperty") after the case ordering but before OtherProperty. – Ally

Answer 2

I don't know anything about ICriteria, but here's a thought. You could try using custom SQL for loading - in other words, a <sql-query> block in your mapping that gives you a sortable column. In Oracle it would be something like this:

<sql-query ...>
  <return ...>
  select *, nvl2(my_column, 1, 0) as not_null
  from my_table
  where id=?
  for update
</sql-query>

Catabasis