没有硬编码列名的Nhibernate QueryOver排序规则

Question

所以我有以下的sql

从名称排序LATIN1_GENERAL_CI_AI的表中选择*，如“myText%”

我想使用QueryOver来实现

就在我有了：

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

很好，但有两个问题。首先它是sqlserver特定的，其次是数据库列'Name‘是硬编码的。

有没有人建议解决这两个问题，或者至少是硬编码的db列名？

Answer 1

我就是这样实现的。不知道有没有更好的方法..。

I.类似的表达，从现有的相似表达中获益

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. .自定义扩展方法

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;
    }
}

三、QueryOverAPI中的任何用法

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