NHibernate.Linq LIKE

Question

How can I produce this query using NHibernate.Linq?

WHERE this_.Name LIKE @p0; @p0 = 'test'  // Notice NO % wild card

Note, this is not Linq To Sql or Entity Framework. This is NHibernate.

Edit:

Here is the desired query using ICriteria:

criteria.Add(Expression.Like("Name", "test"));
return criteria.List<Theater>();

score 27 · Answer 1 · answered Oct 24 '12 at 16:19

27

Whilst this has been marked as resolved, which was correct at the time, may I also note that NHibernate has some extensions now so you can do the following:

Session.QueryOver<MyEntity>()
    .Where(x => x.Property.IsLike("something", MatchMode.Anywhere))
    .List();

This will do a LIKE '%something%' for you.

answered Oct 24 '12 at 16:19

Kieran Senior

17,960
26
94
138

1

These are out-the-box, by the way, not custom built. – Kieran Senior Oct 24 '12 at 16:19
+1 exactly what I was looking for, might be worth noting to solve the exact problem above you'd want to use MatchMode.Exact (no % wildcard) – PJUK Apr 18 '13 at 16:51
2

Had to add a `using NHibernate.Criterion;` when not using Resharper. But works great! Thanks. – Russell B Oct 23 '13 at 19:28
3

The question is about Linq to NHibernate, not about Query over. – Phil Feb 06 '14 at 17:14

score 18 · Answer 2 · answered Nov 08 '09 at 22:15

18

I believe this is what you are looking for:

var theaters = from theater in Session.Linq<Theater>() 
               where theater.Name.Contains("test") 
               select theater;

According to my tests it generates an SQL 'LIKE' statement: "... WHERE theater.Name LIKE %test%"

which is exactly the output of the criteria snippet you have provided.

answered Nov 08 '09 at 22:15

tolism7

2,351
17
25

1

Using Criteria (provided in the question), I am not seeing the % wildcards being generated. – mxmissile Nov 09 '09 at 15:55
I have just ran the unit test that I have with the code provided in my answer and it produced a SQL statement with the % wildcard in both ends on the serch string. I also run the same test using the Criteria equilevant and I get the exact same SQL statement if I use criteria.Add(Expression.Like("Name", "test", MatchMode.Anywhere)); I use NHibernate 2.1 with SQL Server 2005. If you are using the same configuration then you should be able to see the same results. – tolism7 Nov 09 '09 at 17:08
The issue is I don't want the wildcards using Linq. My comment above this was responding to your answer "exactly the output of the criteria snippet you have provided". Sorry if I was not clear. I can achieve the desired query with ICriteria as stated in the Question, but cannot seem to achieve it using Linq. – mxmissile Nov 11 '09 at 14:44
2

So you want to generate a LIKE statment WITHOUT the % around your search string. As you say you are doing that already with Criteria but you want to achieve the same with LINQ. I apologise for misreading your initial comment... Can I ask why would someone want to do that? I believe the LIKE operator without wildcards is the same or at least is optimised to be the same as using the '=' operator. – tolism7 Nov 11 '09 at 15:42
1

Yes, that is my question. The reason why is given the criteria LIKE 'test' returns values with 'TEST' and not 'TESTEST'. Or in other words 'Test' != 'test'. So basically its for ignoring case only. Hope that makes sense. – mxmissile Nov 13 '09 at 19:14

score 18 · Answer 3 · answered Sep 30 '10 at 19:24

18

I had the same problem in my project and found a solution :

session.Linq<Theater>()
    .Where(x => x.Name.StartsWith("test") && x.Name.EndsWith("test");

This translates in SQL to

SELECT ... WHERE Name LIKE '%test' AND Name LIKE 'test%'

answered Sep 30 '10 at 19:24

CMerat

4,398
25
28

4

addition: `&& x.Name.Length == "test".Length`. otherwise you'd end up with results like `test blah blah blah test` – J. Ed Jul 07 '11 at 09:48
6

I may be missing the point, but surely you aren't really doing a 'like' but an 'equals' ... .Where(x => x.Name.Equals("test"); – Mark Powell Jan 18 '13 at 10:06
It's indeed as @n3rd said. – Johnny_D Jul 15 '13 at 14:41
This will not work if the search text is in the middle. For example, if you type in "cks", the word "Jackson" will not come up. – Phil Feb 06 '14 at 17:10
2

I know. The question pertained to a LIKE without wildcards. Check his comment on the first line. – CMerat Feb 06 '14 at 21:39

Frédéric · Accepted Answer · 2016-03-24T15:30:59.037

8

With NH 4 (and probably a bit earlier), a built-in Like string extension is available within NHibernate.Linq namespace: Like(this string matchExpression, string sqlLikePattern). (It is defined on NHibernate.Linq.SqlMethods extension class.)

using NHibernate.Linq;
...
session.Query<Theater>()
    .Where(t => t.Name.Like("test"));

edited Mar 24 '16 at 15:30

answered Mar 24 '16 at 07:05

Frédéric

9,364
3
62
112

NHibernate.Linq LIKE

4 Answers4

Linked