How to avoid a LIKE clause using NHibernate criteria?

Question

The code we use is straightforward in this part of the search query:

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

and it works great in a production environment.

The problem is that one of our customers has product codes containing% characters that this request must 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 get some odd results when searching for items.

Is there a way to enable escaping using Criteria programming methods?

---

Application:

We are using the slightly old version of NHibernate, 2.1.0.4000 (the current one since writing is 2.1.2.4853), but I checked the release notes and there was no mention of a fix for this. I also did not find an open problem in my bugtracker.

We use SQL Server, so I can easily escape the special characters (%, _, [, and ^) in the code, but the point of using NHibernate was to make our database as database independent as possible .

Neither Restrictions.InsensitiveLike() nor HqlQueryUtil.GetLikeExpr() go out of their inputs, and removing the MatchMode parameter MatchMode n't matter as much as possible.

---

Update: I found someone else who wanted to do the same (three years ago), and the permission was to add escapeChar overloads to the methods described above (this was fixed in version 2.0.0.3347). I added a comment on this issue, requiring further resolution.

Answer 1

The only way to find database independence is to escape every character in the search bar and call the corresponding constructor in LikeExpression, as in my previous answer. You can do it 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) {} } 

Without a doubt, a more efficient way to create a shielded string (see answers and comments on this question ). Using:

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

Answer 2

You can create an instance of LikeExpression for this. In this example, I avoid% with a backslash (which itself must be escaped):

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

I have not seen a static method to return a LikeExpression expression using this overload.

By the way, if you use SQL Server, by default it is case insensitive.