CODEWITHSUNDEEP
nhibernatesql-order-by
Mayo
Mayo

Reputation: 10782

NHibernate - truly dynamic sorting

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

The configurable sort:

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?

Upvotes: 1

Views: 1148

Answers (1)

Mayo
Mayo

Reputation: 10782

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.

Upvotes: 2

Related Questions