nhibernate CreateCriteria wildcard Like when

Question

In SQL I can write

SELECT blah FROM Clients Where @p1 Like '%'+lastname+'%'

How do I represent this with CreateCriteria in Nhibernate?

I've tried s.CreateCriteria<Client>.Add(Restrictions.Where<Client>(c => "something".Contains(c.LastName))

but get an error

System.Exception: Unrecognised method call: System.String:Boolean Contains(System.String)\r\n at NHibernate.Impl.ExpressionProcessor.ProcessCustomMethodCall(MethodCallExpression methodCallExpression)

I've also tried

s.CreateCriteria<Client>.Add(Restrictions.Where<Client>(c => "something".IndexOf(c.LastName) != -1))

but get

"variable 'c' of type 'TrinityFinance.Data.Entities.Client' referenced from scope '', but it is not defined"

Note the order is important here.

@p1 Like '%'+lastname+'%'

is not the same as

lastname Like '%'+@p1+'%'

score 13 · Answer 1 · edited Jan 04 '12 at 16:39

13

s.CreateCriteria<Client>().Add(
      Restrictions.InsensitiveLike( "LastName", "something", MatchMode.Anywhere))

edited Jan 04 '12 at 16:39

Mahmoud Gamal

78,257
17
139
164

answered Jan 04 '12 at 15:45

Ben Fulton

3,988
3
19
35

This will produce select * from clients where lastname like '%something%'. **I don't want this.** I want 'select * from clients where "something" like '%'+lastname+'%' – Johnno Nolan Jan 04 '12 at 16:06
That is weird enough that I think you'd have to fall back to Expression.Sql and put the statement in directly. I doubt there's any direct NHibernate support for it. – Ben Fulton Jan 04 '12 at 16:16
1

.Add(Expression.Sql((? like lower('%LastName%')", "something", NHibernateUtil.String )) or something close to that. – Ben Fulton Jan 04 '12 at 16:22
hmm... trouble is how do you get the value '%'+Lastname+'%' – Johnno Nolan Jan 04 '12 at 16:43
I would go for this answer, but i find the question kinda strange. If you do go for this answer, please note that if your database is case insentive you could just go for a like, that will use your indexes better. – Nexus Jan 05 '12 at 08:38

Johnno Nolan · Accepted Answer · 2012-01-05T10:12:34.803

1

Thanks to a friend I've solved my issue.

var searchCriteria = GetSession().CreateCriteria<Client>(); searchCriteria.Add(Expression.Sql(string.Format("'{0}' like '%' + {1} + '%'", p.ClientInputText,p.DbField)));

var results = searchCriteria.List<Client>();

edited Jan 05 '12 at 10:12

answered Jan 04 '12 at 15:02

Johnno Nolan

29,228
19
111
160

2

Note that this is vulnerable to injection attacks, assuming that ClientInputText comes from a web page. What happens if ClientInputText is "anything' OR 'x'='x'; --" ? – Ben Fulton Jan 05 '12 at 14:00
Yep I agree... not sure there is a better solution. – Johnno Nolan Jan 05 '12 at 14:54

score 0 · Answer 3 · answered Apr 23 '15 at 13:38

0

For Case-Insensitive %Like% Search

 Criteria criteria = session.createCriteria(Any.class);
 criteria.add(Restrictions.ilike(propertyName, value, MatchMode.ANYWHERE);
 criteria.list();

answered Apr 23 '15 at 13:38

MADHAIYAN M

2,028
25
22

nhibernate CreateCriteria wildcard Like when

3 Answers3

Linked