Nhibernate QueryOver sort without hard coded column name

Question

So I have the following sql

SELECT * FROM table Where Name COLLATE LATIN1_GENERAL_CI_AI LIKE 'myText%'

which I want to implement using QueryOver

At that moment when I have:

whereRestriction.Add(Expression.Sql("Name COLLATE LATIN1_GENERAL_CI_AI LIKE ?", String.Format("{0}%", subStringMatch), HibernateUtil.String)); 

which works great, but with two problems. Firstly, it is sqlserver specific, and secondly, the database column "Name" is hard-coded.

Does anyone have any suggestions to get around these two issues, or at least the hardcoded db column name?

Answer 1

I implemented it that way. Not sure if there is a better way ...

I. A similar expression that benefits from an existing Like expression

 public class LikeCollationExpression : LikeExpression { const string CollationDefinition = " COLLATE {0} "; const string Latin_CI_AI = "LATIN1_GENERAL_CI_AI"; // just a set of constructors public LikeCollationExpression(string propertyName, string value, char? escapeChar, bool ignoreCase) : base(propertyName, value, escapeChar, ignoreCase) { } public LikeCollationExpression(IProjection projection, string value, MatchMode matchMode) : base(projection, value, matchMode) { } public LikeCollationExpression(string propertyName, string value) : base(propertyName, value) { } public LikeCollationExpression(string propertyName, string value, MatchMode matchMode) : base(propertyName, value, matchMode) { } public LikeCollationExpression(string propertyName, string value, MatchMode matchMode, char? escapeChar, bool ignoreCase) : base(propertyName, value, matchMode, escapeChar, ignoreCase) { } // here we call the base and append the COLLATE public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters) { // base LIKE var result = base.ToSqlString(criteria, criteriaQuery, enabledFilters); var sqlStringBuilder = new SqlStringBuilder(result); // extend it with collate sqlStringBuilder.Add(string.Format(CollationDefinition, Latin_CI_AI )); return sqlStringBuilder.ToSqlString(); } } 

II. custom extension method

 public static class QueryOverExt { // here: WhereLikeCiAi() public static IQueryOver<TRoot, TSubType> WhereLikeCiAi<TRoot, TSubType>( this IQueryOver<TRoot, TSubType> query , Expression<Func<TSubType, object>> expression , string value , MatchMode matchMode) { var name = ExpressionProcessor.FindMemberExpression(expression.Body); query .UnderlyingCriteria .Add ( new LikeCollationExpression(name, value, matchMode) ); return query; } } 

III. using anyhwere in QueryOverAPI

 ... query.WhereLikeCiAi(c => c.Name, "searchedString", MatchMode.Anywhere);