How to query for data containing square brackets when using NHibernate.Linq Query

Question

Some records in my database contain square brackets, eg.

dafalgan [1gram]
valium [15mg]
...

However when I want to query this with NHibernate.Linq using the following expression

return x => x.Description.Contains(searchTerm)

where searchTerm is 'dafalgan [1gram]' the following SQL is used/generated:

SELECT        FieldName1, FieldName2
FROM            TableName
WHERE        (Description LIKE '%' + N'dafalgan [1gram]' + '%')

This doesn't return any results because SQL Server considers [ to be the start of a regular expression match for any one of the characters between brackets. This is only an issue with SQL Server, pl-sql does not consider [ to be a special character.

NH-938 mentions an overload for Restrictions.Like allowing to pass an escape character but I have no control over the code used (part of NHibernate.Linq).

I know I can do a searchTerm.Replace("[", "[[]"); in my code but I was looking for a more general solution, eg. adding it to Hibernate.Dialect.MsSql2000Dialect because it's specific only to SQL Server.

See this answer (http://stackoverflow.com/questions/2492984/how-do-i-escape-a-like-clause-using-nhibernate-criteria) and comments for how to escape the expression in the Criteria API. I don't know how to do it in LINQ, good question. — Jamie Ide, Nov 09 '12 at 14:44

score 0 · Accepted Answer · answered Mar 21 '19 at 15:09

Based on @Jamie Ide 's link, the following works for me:

    private static AbstractCriterion MatchModeAnywhereSafeCriterion(string propertyName, string value)
    {
        const char escapeChar = '\u001B';
        var safeValue = value.Replace("%", escapeChar + "%").Replace("_", escapeChar + "_")
            .Replace("[", escapeChar + "[");
        return Restrictions.Like(propertyName, safeValue, MatchMode.Anywhere, escapeChar);
    }

How to query for data containing square brackets when using NHibernate.Linq Query

1 Answers1