没有硬编码列名的Nhibernate QueryOver排序规则 [英] Nhibernate QueryOver collation without hard coded column name

问题描述

所以我有以下sql

SELECT * FROM表其中名称COLLATE LATIN1_GENERAL_CI_AI LIKE'myText％'

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

我想使用QueryOver实现的

which I want to implement using QueryOver

目前我有:

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

可以正常工作，但是有两个问题.首先是特定于sqlserver，其次是对数据库列名称"进行了硬编码.

which works fine, but with two issues. Firstly it's sqlserver specific and secondly the database column 'Name' is hardcoded.

有人对解决这两个问题有任何建议吗，或者至少是硬编码的db列Name?

Has anyone any suggestions to get around these two problems, or at the very least the hardcoded db column Name?

推荐答案

我已经以这种方式实现了它.不确定是否还有更好的方法...

I've implemented it this way. Not sure if there is any better way...

I.从现有的Like表达式中获利的like表达式

I. The like expression, profiting from the 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.自定义扩展方法

II. the 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.用法在QueryOverAPI中

III. the usage anyhwere in the QueryOverAPI

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

这篇关于没有硬编码列名的Nhibernate QueryOver排序规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！