Cannot Compare Strings during nHibernate QueryOver

Question

I've got a simple method to try and validate users when they login,and I use Fluent nHibernate for persistence, so I naturally implement an ISession.QueryOver<T> to do this work.

It looks like the following.

var member = session.QueryOver<Member>()
   .Where(m => m.Email == Model.Email)
   .Take(1).SingleOrDefault();

Ok. So then, the problems at hand.

1. Email addresses always need to be compared case-insensitive.

They should always be in the database as lowercase. I have gone to great pains to make this happen. And in fact, my <input> that accepts the Email Address has a validation rule on it to only allow lowercase letters. But that's still not good enough, I want to make this even deeper and make absolutely sure that everything is Kosher.

So I tried doing this...

var member = session.QueryOver<Member>()
   .Where(m => String.Compare
         (m.Email, Model.Email, StringComparison.OrdinalIgnoreCase) == 0)
   .Take(1).SingleOrDefault();

I get an exception that nhibernate cannot use the String.Compare method.

I realize I can solve this with just the plain ToLower() method, but there may be situations where I want a bit more granularity over other kinds of comparisons.

Can someone help me figure out how to get around this?

Answer 1

There are multiple ways of doing this, with a IsInsensitiveLike:

   var member= Session.QueryOver<Member>()
       .WhereRestrictionOn(m=>m.Email).IsInsensitiveLike(Model.Email)
       .Take(1).SingleOrDefault();

Answer 2

If @VahidN's answer of leaning on the default collation and/or specifying an explicit one it doesn't work, one can drop to SQL-dialect-specific case conversion like so:

return _session.QueryOver<Registration>()
        .WhereEqualsIgnoreCase(r => r.Name, userName)   
        .Future().SingleOrDefault();

Implemented as follows:

static class NHibernateCaseInsensitiveWhereExtensions
{
    public static IQueryOver<T, T2> WhereEqualsIgnoreCase<T, T2>(this IQueryOver<T, T2> that, Expression<Func<T, object>> column, string value)
    {
        return
            that.Where(
                Restrictions.Eq(
                    Projections.SqlFunction(
                        "upper", 
                        NHibernateUtil.String,
                        Projections.Property(column)),
                    value.ToUpper()));
    }
}

Answer 3

SQL Server Text Matching Is Case INSENSITIVE. If you don't like that, you have to change the collation (SQL_Latin1_General_CP1_CS_AS). So you don't need to change anything (server side or client side).