How do I escape a LIKE clause using NHibernate Criteria?
- by Jon Seigel
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.
