CreateCriteria sorting on two columns combined

Question

SELECT [quantity ]
      ,[price]
FROM [dbo].[orderItem]
ORDER BY (quantity  * price) DESC

How should I write a CreateCriteria to generate SQL like the example above?

Answer 1

Base on the fragment, I guess, that you've already done all the mappings. You also would have criteria similar to this snippet:

var criteria = session
  .CreateCriteria<OrderItem>()  // class with properties quantity, price ...
  .AddOrder(?) // how to append the order
  ;
var list = criteria.List<OrderItem>(); // Sorted list of OrderItems

The only missing point is the OrderBy. Thanks NHibernate extensibility, we can create our own Order object, and the when building the Criteria, we can call .AddOrder(CustomOrder...) Here is the code:

public class CustomOrder : Order
{
    public CustomOrder(string propertyName, bool ascending) 
        : base(propertyName, ascending) { }

    public override SqlString ToSqlString(ICriteria criteria
                                        , ICriteriaQuery criteriaQuery)
    {
        var sqlString = new SqlString(propertyName);
        return sqlString.Append(this.ascending ? " asc" : " desc");
    }
}

So, now we can put it all together:

var criteria = session
  .CreateCriteria<OrderItem>() 
  .AddOrder(new CustomOrder("(quantity  * price)", false)
  ;
var list = criteria.List<OrderItem>(); // Sorted list of OrderItems