Does NHibernate LINQ support ToLower() in Where() clauses?

Question

I have an entity and its mapping:

public class Test
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual string Description { get; set; }
}

public class TestMap : EntityMap<Test>
{
    public TestMap()
    {
        Id(x => x.Id);
        Map(x => x.Name);
        Map(x => x.Description);
    }
}

I'm trying to run a query on it (to grab it out of the database):

var keyword = "test" // this is coming in from the user
keyword = keyword.ToLower(); // convert it to all lower-case

var results = session.Linq<Test>
    .Where(x => x.Name.ToLower().Contains(keyword));

results.Count(); // execute the query

However, whenever I run this query, I get the following exception:

Index was out of range. Must be non-negative and less than the size of the
collection. Parameter name: index

Am I right when I say that, currently, Linq to NHibernate does not support ToLower()? And if so, is there an alternative that allows me to search for a string in the middle of another string that Linq to NHibernate is compatible with? For example, if the user searches for kap, I need it to match Kapiolani, Makapuu, and Lapkap.

Why do you think the problem is in the call to ToLower() - it is a standard method defined on the String class and should have nothing to do with NHibernate LINQ. At which line in the source above do you get the exception? — Slavo, Apr 08 '10 at 07:55
On the `results.Count()`, since that's when it's evaluated. And it has a LOT to do with Linq to NHibernate, since it needs to convert it to SQL. — Daniel T., Apr 08 '10 at 08:08
That Linq provider is quite old and hasn't been updated for a while. It might work, but you might also want to try the newer Linq provider in the NHibernate trunk. — Michael Maddox, Apr 08 '10 at 09:22
Thanks Michael. I should mention that I'm using NHibernate.Linq 2.1.2 GA. I had no idea NHibernate 3 was out already. — Daniel T., Apr 08 '10 at 19:41

score 11 · Accepted Answer · answered Apr 08 '10 at 11:58

11

I had this happen recently. I can tell you that ToLower() does not work and that Contains() and StartsWith() do work and are not case sensitive. You can get the desired affect by using Contains() and StartsWith() directly.

answered Apr 08 '10 at 11:58

ddc0660

4,022
2
21
30

Doh, I can't believe I didn't notice that `Contains()` and `StartsWith()` are case-insensitive. I wasted 5 hours yesterday trying to get `ToLower()` to work when it's already implemented! – Daniel T. Apr 08 '10 at 20:04

score 11 · Answer 2 · edited Oct 24 '13 at 17:19

There seems to be a lot of confusion around this subject.

The "old" Linq provider (for NHibernate 2.x) probably might not support this. If that's the case, it never will because it's not maintained anymore.
The new provider (included with NHibernate 3.x) does support it (although ToUpper and ToLower seem to be inverted, see http://groups.google.com/group/nhibernate-development/browse_thread/thread/a167216e466b3241)
Contains and StartsWith map to the LIKE operator in SQL. They are not case insensitive themselves; it's the collation that makes them case insensitive, so that depends on how your column/schema were created.

Update (2010-04-09): bug confirmed and patch submitted, see https://nhibernate.jira.com/browse/NH-2169

Update (2010-05-21): patch was applied on 2010-05-01 and works as expected now.

so, what version has the fix? – Louis Rhys Feb 27 '12 at 09:29 — Louis Rhys, Feb 27 '12 at 09:29
@LouisRhys just get the latest stable. – Diego Mijelshon Feb 27 '12 at 11:24 — Diego Mijelshon, Feb 27 '12 at 11:24

Darin Dimitrov · Answer 3 · 2010-04-08T07:53:37.620

1

According to the comments in these two blog posts this functionality is not implemented yet.

edited Apr 08 '10 at 07:53

answered Apr 08 '10 at 07:47

Darin Dimitrov

1,023,142
271
3,287
2,928

That NHUsers Google Groups thread actually implies that it does work. – Michael Maddox Apr 08 '10 at 09:21
@Michael, I didn't find any evidence in this thread suggesting that it works. – Darin Dimitrov Apr 08 '10 at 09:37

score 0 · Answer 4 · answered Apr 08 '10 at 11:53

0

You might want to confirm whether the database uses case sensitivity.

If it doesn't, then you don't need .ToLower()

answered Apr 08 '10 at 11:53

Amy B

108,202
21
135
185

score 0 · Answer 5 · answered Aug 28 '19 at 10:08

The accepted answer mentions using Contains() and StartsWith() which are good. but wouldn't work in cases when you want to be sure both strings are the same.

Using "==" will suffice since it is also case-insensitive. So, you no longer need to use ToLower() nor ToUpper();

Does NHibernate LINQ support ToLower() in Where() clauses?

5 Answers5

Linked