NHibernate SqlAliasCriterion and Random sorting
Wrote this custom NHibernate Criterion to be able to add custom SQL snippets with alias support when using criteria.
public class SqlAliasCriterion : SQLCriterion { private string sql; public SqlAliasCriterion(string sql) : base(new SqlString(sql), new string[0], new IType[0]) { this.sql = sql; } public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters) { string replacedString = Regex.Replace( sql, @"{([a-zA-Z_]((\.)?[a-zA-Z0-9_])*)}", m => { ICriteria critter = criteria.GetCriteriaByAlias(m.Groups[1].Value); if (critter != null) { return criteriaQuery.GetSQLAlias(critter); } return m.Groups[0].Value; } ); return new SqlString(replacedString); } } |
Usage example:
return Session.CreateCriteria<YourClass>("x") .Add(new SqlAliasCriterion("{x}.YourField = SomeNativeSQLFunc(foo)")) .List<YourClass>(); |
You can use the same trick to do ordering. (We use this for native MSSQL random ordering)
public class OrderBySql : Order { private string sql; public OrderBySql(string sql, bool ascending) : base(String.Empty, ascending) { this.sql = sql; } public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery) { string replacedString = Regex.Replace( sql, @"{([a-zA-Z_]((\.)?[a-zA-Z0-9_])*)}", m => { ICriteria critter = criteria.GetCriteriaByAlias(m.Groups[1].Value); if (critter != null) { return criteriaQuery.GetSQLAlias(critter); } return m.Groups[0].Value; } ); return new SqlString(replacedString + (this.ascending ? " ASC" : " DESC")); } } |
Example with random sorting (MSSQL):
criteria.addOrder( new OrderBySql("NEWID()") ); |