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.

Frustum

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? – Accommodate

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

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

Thanks Michael. I should mention that I'm using NHibernate.Linq 2.1.2 GA. I had no idea NHibernate 3 was out already. – Frustum

Answer 1

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.

Vicechancellor

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! – Frustum

Answer 2

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.

Verified

so, what version has the fix? – Violent

@LouisRhys just get the latest stable. – Verified

Answer 3

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

Marauding

That NHUsers Google Groups thread actually implies that it does work. – Musso

@Michael, I didn't find any evidence in this thread suggesting that it works. – Marauding

Answer 4

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

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

Bidget

Answer 5

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

Wills