Unable to compare rows during nHibernate QueryOver <T>

Question

I have an easy way to try and verify users at login, and I use Fluent nHibernate to save, so I naturally implement ISession.QueryOver<T> to do this work.

It looks as follows.

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

Ok So the problems.

• Email addresses must always be case-insensitive.

They should always be in the database as lowercase. I did my best to make this happen. And in fact, my <input> , which accepts Email Address , has a validation rule on it to allow only lowercase letters. But this is not enough, I want to do it even deeper and I am absolutely sure that everything is kosher.

So I tried to do 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 understand that I can solve this with the simple ToLower() method, but there may be situations where I want a little more detail compared to other types of comparisons.

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

Answer 1

There are several ways to do this with IsInsensitiveLike:

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

Answer 2

If the @VahidN answer, based on the default sort and / or specifying an explicit option, does not work, you can proceed to the SQL dialect like this:

 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 Alignment is Extreme. If you don't like this, you need to change the mapping ( SQL_Latin1_General_CP1_CS_AS ). Therefore, you do not need to change anything (on the server side or on the client side).