Archive

Posts Tagged ‘nhibernate’

NHibernate SqlAliasCriterion and Random sorting

December 30th, 2011 No comments

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()")
);
Categories: C# Tags: , , ,