Nhibernate Queryover Case Insensitive IsIn

Question

I've got this example code...

Result = session.QueryOver<Lfee_Exc>().WhereRestrictionOn(x => x.FirstName)
.IsIn(ListOfFirstNames).List();

Is there any way to make this case insenstive or upper case x.ArNumber for my case sensitive Oracle server?

Marquetry

Don't think it is possible with IsIn as the operation is not available in ICriteria. (QueryOvers underlying implementation is resolved to criteria.) There is an IsInsensitiveLike lambda restriction operator though. – Kendry

Answer 1

Convert ListOfFirstNames to upercase and then:

session.QueryOver<Lfee_Exc>()
    .Where(Restrictions.In(Projections.SqlFunction(
                              "upper", NHibernateUtil.String,
                               Projections.Property<Lfee_Exc>(x => x.FirstName)),
                           ListOfFirstNames))

Wonderment

Thanks. Is there a list of the built in sqlfunctions out there? Can't seem to find an exhaustive list on google. – Marquetry

@gt124: They vary depending on the dialect, standard ones are in nhibernate.svn.sourceforge.net/svnroot/nhibernate/trunk/…, look for specific ones in nhibernate.svn.sourceforge.net/svnroot/nhibernate/trunk/… (keep the inheritance chain in mind: MsSql2008 inherits from MsSql2005, etc) – Wonderment

@DiegoMijelshon do you happen to have the updated links to the above resources? (I know this answer is 8 years old so totally understand if you aren't working on NHibernate stuff anymore) – Piercy

@Piercy new root is github.com/nhibernate/nhibernate-core, so that would be github.com/nhibernate/nhibernate-core/tree/master/src/… I'm most definitely not doing any NHibernate development, but there's a special place in my heart for it :-) – Wonderment

@DiegoMijelshon I thought I was through with it as well, but have been sucked back in. Thankfully it’s like riding a bike comes right back. – Piercy

Answer 2

Currently using NHibernate 5.3 and this worked for me.

First make ListOfFirstNames upper case and then use ProjectionExtension Upper() from NHibernate.Criterion namespace in the query.

using NHibernate.Criterion;

var upperNames = ListOfFirstNames.Select(name => name.ToUpper()).ToArray<object>();

var result = session.QueryOver<Lfee_Exc>()
    .Where(x => x.FirstName.Upper().IsIn(upperNames))
    .List();

Jermayne