我该如何逃生使用NHibernate标准LIKE子句？ [英] How do I escape a LIKE clause using NHibernate Criteria?

问题描述

我们所使用的code是直截了当的这部分搜索查询的：​​

The code we're using is straight-forward in this part of the search query:

myCriteria.Add(
    Expression.InsensitiveLike("Code", itemCode, MatchMode.Anywhere));

和这个工作正常，在生产环境中。

and this works fine in a production environment.

问题是，我们的一个客户有项目codeS包含此查询需要符合哪些％符号。从这个code SQL输出生成的是类似于：

The issue is that one of our clients has item codes that contain % symbols which this query needs to match. The resulting SQL output from this code is similar to:

SELECT ... FROM ItemCodes WHERE ... AND Code LIKE '%ItemWith%Symbol%'

这清楚地解释了为什么他们得到一些奇怪的结果项的搜索。

which clearly explains why they're getting some odd results in item searches.

有没有一种方法，使使用的编程标准方法逃避？

Is there a way to enable escaping using the programmatic Criteria methods?

附录：

我们正在使用一个稍微旧版本的NHibernate的，2.1.0.4000（目前的写作是2.1.2.4853），但我查了发行说明，也没有提到这个的修复程序。我没有发现任何悬而未决的问题在他们的错误追踪系统无论是。

We're using a slightly old version of NHibernate, 2.1.0.4000 (current as of writing is 2.1.2.4853), but I checked the release notes, and there was no mention of a fix for this. I didn't find any open issue in their bugtracker either.

我们正在使用的SQL Server，这样我就可以逃脱特殊字符（％，_，[和^）在code其实很容易，但我们使用NHibernate的一点是让我们的应用程序的数据库引擎非依赖性尽可能

We're using SQL Server, so I can escape the special characters (%, _, [, and ^) in code really easily, but the point of us using NHibernate was to make our application database-engine-independent as much as possible.

无论是 Restrictions.InsensitiveLike（）和 HqlQueryUtil.GetLikeExpr（）逃脱他们的投入，并删除 MatchMode 参数都没有区别尽可能逃避去。

Neither Restrictions.InsensitiveLike() nor HqlQueryUtil.GetLikeExpr() escape their inputs, and removing the MatchMode parameter makes no difference as far as escaping goes.

更新：我发现别人想做同样的事（三年前），并且分辨率是到 escapeChar 重载添加到我上面提到的方法（这是在2.0.0.3347版本的固定）。我添加了评论这个问题，要求进一步解决。

Update: I found someone else wanting to do this same thing (three years ago), and the resolution was to add the escapeChar overloads to the methods I've mentioned above (this was "fixed" in version 2.0.0.3347). I added a comment to that issue asking for further resolution.

推荐答案

我能找到实现数据库的独立性，唯一的办法就是逃跑的每个字符的搜索字符串，并呼吁LikeEx pression适当的构造函数在我previous答案。你可以手动执行此操作或延长LikeEx pression：

The only way I can find to achieve database independence is to escape every character in the search string and call the appropriate constructor on LikeExpression as in my previous answer. You could do this manually or extend LikeExpression:

    public class LikeExpressionEscaped : LikeExpression
    {
        private static string EscapeValue(string value)
        {
            var chars = value.ToCharArray();
            var strs = chars.Select(x => x.ToString()).ToArray();
            return "\\" + string.Join("\\", strs);
        }

        public LikeExpressionEscaped(string propertyName, string value, MatchMode matchMode, bool ignoreCase)
            : base(propertyName, EscapeValue(value), matchMode, '\\', ignoreCase)
        {}
    }

毫无疑问，一个更有效的方法来创建转义字符串（见的答案和评论<一个href="http://stackoverflow.com/questions/145856/how-to-join-int-to-a-character-separated-string-in-net">this问题）。用法是：

var exp = new LikeExpressionEscaped("Code", itemCode, MatchMode.Anywhere, true);
myCriteria.Add(exp);

这篇关于我该如何逃生使用NHibernate标准LIKE子句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！