How do I escape a LIKE clause using NHibernate Criteria?

Question

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.

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?

---

Addendum:

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.

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.

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

---

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.

Answer 1

You can create an instance of LikeExpression to accomplish this. In this example I am escaping % with a backslash (which itself has to be escaped):

var itemCode = "ItemWith%Symbol";
itemCode = searchCode.Replace("%", "\\%");
var exp = new LikeExpression("Code", itemCode, MatchMode.Anywhere, '\\', true);
myCriteria.Add(exp);

I didn't see a static method to return a LikeExpression using this overload.

By the way, if you're using SQL Server it is case insensitive by default.

Answer 2

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)
        {}
    }

There is no doubt a more efficient way to create the escaped string (see the answers and comments to this question). Usage is:

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