NHibernate - truly dynamic sorting

Question

Using NHibernate, I need to be able to configure my application to sort a specific collection of entities exactly as needed.

The configurable sort:

can involve multiple properties
can specify the sorted properties in any order
can specify asc/desc on the sorted properties
can sort by custom properties (i.e. there is no corresponding SQL/C# property - it is calculated)

This functionality is inherited from an existing app where parts of the SQL are specified by an administrator and the SQL statement is built/executed dynamically.

Every time I try thinking through a solution I start getting in muddy waters with all kinds of alarms going off in my head regarding maintainability, performance, scalability, security, etc..

For example, I figure the admin can specify a comma delimited string like so:

"Date asc, FirstName asc, LastName desc"

I can split the string and go through a loop matching the property/sort pairings in a case statement and calling .AddOrder(Order.Asc("FirstName")) as necessary. But then, how do I handle custom properties? I could allow the user to specify SQL for calculating custom properties and then allow the user to sort on those like they would FirstName, but I'm seemingly back at dirty/kludge again.

Is there a clean/appropriate way to handle this requirement?

Mayo Mayo · Accepted Answer · 2011-05-10T13:18:55

After much thought and a stroke of luck, I may have a solution.

public class CustomOrder : Order
{
  private string customOrderSql;

  public CustomOrder(string customOrderSql) : base("", true)
  {
    this.customOrderSql = customOrderSql;
  }

public override NHibernate.SqlCommand.SqlString ToSqlString(
    ICriteria criteria, ICriteriaQuery criteriaQuery) 
  { 
    return new NHibernate.SqlCommand.SqlString(this.customOrderSql); 
  }

}

I can pass a custom sort string to my repository where I add my CustomOrder as follows:

.AddOrder(new CustomOrder(customSort))

I still can't sort by custom properties but maybe I can get away with applying case statements in the order by clause. I'm still open for better suggestions if they exist.

NHibernate - truly dynamic sorting

1 Answers