16

How to do a case-insensitive where in NHibernate Linq query?

e.g. 

//note this one doesn't work if the entry in database has lowercase
q => q.Where(entity => 
    entity.CaseInsensitiveField == DesiredField.Trim().ToUpper())
Justin Pihony
  • 66,056
  • 18
  • 147
  • 180
Louis Rhys
  • 34,517
  • 56
  • 153
  • 221

2 Answers2

15

Try this:

q => q.Where(entity => entity.CaseInsensitiveField.ToUpper() == DesiredField.Trim().ToUpper())

Also, I would suggest to set the parameter outside the query:

var value = DesiredField.Trim().ToUpper();

...

q => q.Where(entity => entity.CaseInsensitiveField.ToUpper() == value)
Ivo
  • 8,172
  • 5
  • 27
  • 42
  • Yes it will, as the ToUpper() method will be translated as UPPER(field) in your SQL query. – Will Marcouiller Mar 23 '12 at 03:19
  • ivowiblo: I humbly confess you were right, my ContainsLike method will not be translated into SQL. Please see my comment to my answer for further details. Thanks to you, with all my humility. – Will Marcouiller Mar 23 '12 at 04:12
5

Use this:

q => q.Where(entity => 
    String.Equals(entity.CaseInsensitiveField , CaseInsensitiveField , 
                  StringComparison.OrdinalIgnoreCase));

UPDATE

It appears (at least via LinqPad) that the above will not translate into SQL, so I would suggest, as ivowiblo has already suggested:

var comparisonValue = CaseInsensitiveField.ToUpper();
q => q.Where(entity => 
    entity.CaseInsensitiveField.ToUpper() == comparisonValue);

The reason to set the ToUpper beforehand is that procedural functions are generally worse in SQL, and since this is not a database field, we can send it in already capitalized.

Justin Pihony
  • 66,056
  • 18
  • 147
  • 180
  • `string.Equals` or `entity.CaseInsensitiveField ==` are the same, so yes it is translated as a simple comparison just like you do. – Will Marcouiller Mar 23 '12 at 03:25
  • Actually, I just updated my answer. LinqPad is saying this will not translate into SQL. So, I updated my response. It ends up being the same as ivowiblo, however I provided the reasoning for the breaking out of the variable – Justin Pihony Mar 23 '12 at 03:37
  • Thanks for mentioning LINQPad, I just tried it and I now know how to determine what can and cannot be translated to SQL. – Will Marcouiller Mar 23 '12 at 04:07
  • 1
    Note: LINQPad doesn't use NHibernate, which the question is asking for. – dav_i Dec 19 '14 at 10:28