Home > C# > NHibernate SqlAliasCriterion and Random sorting

NHibernate SqlAliasCriterion and Random sorting

December 30th, 2011 Leave a comment Go to 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: , , ,
  1. No comments yet.
  1. No trackbacks yet.

Time limit is exhausted. Please reload CAPTCHA.