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

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