PostgreSQL with NHibernate problem with LIKE and parameter.

Question

I have a problem with PostgeSQL and NHibernate. I have tried HQL and Criteria and select returns nothing. I used log4net to get generated query, replaced parameters with value and query returns result, why is that? Is there a special configuration for Postgre or NHibernate? I even tried with lowering (lower(column) like lower(:param)) and no result.

HQL : return sess.CreateQuery("from VersFile e where e.ConfigId = :confId and lower(e.FolderPath) like lower(:path) and e.Type = :type and e.Statuss = :statuss and e.Tester != '' order by e.Sequence asc").SetString("path",path).SetInt32("type", fileType).SetInt32("confId", configId).SetInt32("statuss", recordStatuss).List<VersFile>();

FolderPath is character varying column

OR Criteria

return sess.CreateCriteria(typeof(VersFile)).Add(Expression.Eq("Type", fileType)).Add(Expression.Eq("Statuss", recordStatuss)).Add(Expression.Eq("ConfigId", configId)).Add(Expression.Like("FolderPath", path, MatchMode.Start)).Add(Expression.Not(Expression.Eq("Tester", ""))).Add(Expression.Not(Expression.Eq("Statuss", (int)RecordStatuss.Merged))).AddOrder(Order.Asc("Sequence")).List<VersFile>();

Generated query looks like

select .... from versfile versfile0_ where ( versfile0_.statuss!=99) and versfile0_.confId=:p0 and (lower(versfile0_.fpath) like lower(:p1)) order by versfile0_.sequen asc;:p0 = 1, :p1 = '\%MainVss\%WinGui\%'

I've removed useless parameters from query string

Answer 1

Actually, the '\%MainVss\%WinGui\%' isn't quite valid in modern postgreses. It's either E'\%MainVss\%WinGui\%' or '%MainVss%WinGui%'. Which is equivalent to '%MainVss%WinGui%', which is not necessarily what you want it to be.