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>();

Sharlasharleen

Answer 1

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"));

Pettifog

Answer 2

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.

Despondency

These are out-the-box, by the way, not custom built. – Despondency

+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) – Briarroot

Had to add a using NHibernate.Criterion; when not using Resharper. But works great! Thanks. – Leeanneleeboard

The question is about Linq to NHibernate, not about Query over. – Linnealinnean

Answer 3

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.

Unsubstantial

Using Criteria (provided in the question), I am not seeing the % wildcards being generated. – Sharlasharleen

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. – Unsubstantial

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. – Sharlasharleen

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. – Unsubstantial

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. – Sharlasharleen

Answer 4

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%'

Ocarina

addition: && x.Name.Length == "test".Length. otherwise you'd end up with results like test blah blah blah test – Peggypegma

I may be missing the point, but surely you aren't really doing a 'like' but an 'equals' ... .Where(x => x.Name.Equals("test"); – Gleich

It's indeed as @n3rd said. – Extravagance

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. – Linnealinnean

I know. The question pertained to a LIKE without wildcards. Check his comment on the first line. – Ocarina