C# check string contain a word with underscores

Question

I want to get all items which contain "_AT_" or "PV_", but d.Nom.Contains($"PV_") and d.Nom.Contains($"_AT_") gets also items containing only "AT" and "PV"

 IQueryable<DocumentMetadata> docPV = NHibernateSession.Current.Query<DocumentMetadata>()
           .Where(d => d.IdEntiteRattachement == missionId
                       && d.Nom.Contains($"PV_")
                       && d.Desactive == false)
           .OrderByDescending(d => d.DateDerniereModif);

        IList<DocumentMetadata> docAR = NHibernateSession.Current.Query<DocumentMetadata>()
           .Where(d => d.IdEntiteRattachement == missionId
                       && d.Nom.Contains($"_AT_")
                       && d.Desactive == false)
           .OrderByDescending(d => d.DateDerniereModif).ToList();

Answer 1

In SQL, underscore (and percent) are wild cards. NHibernate doesn't automatically escape them, because you can make use of them. Behind .Contains, there is SQL's LIKE.

Escaping wildcards depend on the underlying DBMS.

Try this:

d.Nom.Contains("\\_AT\\_")

(It may not work. See the docs of your database engine.)

Answer 2

Somehow none of the solutions given above would escape the underscore properly (I tried \\_ @\_ [_] $_ ^_) so I ended up filtering again in pure LINQ:

var list = dc.Employees.Where(a => a.Name.Contains(partial)).ToList();
if (partial.Contains("_")) // underscore is an sql wildcard character and will match anything, so filter again in .NET linq
    list = list.Where(a => a.Name.Contains(partial)).ToList();

This is obviously not the best solution since it retrieves a bigger amount of rows from the DB than necessary and filters again in memory, but in my case the overhead is acceptable.