NH-2099 - Linq Expressions and case sensitivity

[nhibernate-bot]

Jose F. Romaniello created an issue — 2nd February 2010, 7:15:03:

The following query:
persons.Where(p => p.Nombre.StartsWith("a"))

in objects is case-sensitive. And I would like in nhibernate be case sensitive too.

otherwise:
persons.Where(p => p.Nombre.StartsWith("a", StringComparison.OrdinalIgnoreCase))

In sql server it is possible to change the "coalesce" in the where statement. I don't know for other providers.
The same for others string operators.

---

Jeffrey Cameron added a comment — 10th April 2010, 7:08:41:

This would be great to have!. In the meantime ... is there a workaround?

---

Patrick Earl added a comment — 30th October 2010, 21:44:07:

If your column stores case sensitive data, you can change the collation on the column itself. Then any queries will behave as expected automatically. Since other databases are case sensitive by default, I would argue that you should use the database setting to make the search case sensitive by default. It might be difficult for NHibernate to guess which collation the user is actually looking for. At the very least, it would need to be dialect specific.

If you use SQL Server Management Studio, you can set the Collation setting in the column properties within the table designer.

---

Fabio Maulo added a comment — 24th November 2010, 4:42:09:

Patrick,
A lot of things are "dialect specific".
The StringComparison should be parsed and translated, at least if we want have a real LINQ provider.

---

Patrick Earl added a comment — 24th November 2010, 8:06:18:

I'm good with translating the StringComparison part. The tricky part is getting SQL server out of its case insensitive mode. As far as I can tell, you'd need to determine the collation setting of a column and then perform some sort of automatic mapping between the case insensitive collation and the case sensitive collation. Then you'd need to use that collation in the query.

See method 2 for an example:
http://vyaskn.tripod.com/case*sensitive_search_in_sql*server.htm

The current behavior is consistent with Linq to SQL:
http://yourdotnetdesignteam.blogspot.com/2010/06/case-sensitive-linq-to-sql-queries.html

Given this evidence, I would recommend the use of case sensitive collations on columns with case sensitive data. The only case where this may be a problem is in legacy databases where the collation cannot be modified. However, I believe this would be sufficiently rare that the user could write more a more customized query and skip the Linq provider.

This leads me to another question that I don't have time to look up at this exact moment... does NHibernate do automatic collation mapping elsewhere?

---

Patrick Earl added a comment — 28th November 2010, 13:14:37:

To answer my previous question, a quick search for "collation" leads to no relevant results.

---

Mattias Öhrn added a comment — 7th June 2011, 9:01:10:

I actually have a scenario where this would be useful even if you have full control of the DB structure. In MySQL (and maybe other DB brands) the collation of a column is used for all comparisons, including unique constraint checking. So if a unique constraint must be case sensitive a case sensitive collation must be used even if you know that you want to run case insensitive queries towards this column.

---

Fabio Maulo added a comment — 7th June 2011, 15:53:44:

Patrick,
what about this:

• StartsWith found with 2 parameters
• second parameter is StringComparison.OrdinalIgnoreCase
  then apply "ilike" HQL function.

We have to register "ilike" as default HQL-function in base Dialect and then each dialect can override it with a specific "implementation".

Thoughts ?

---

Jose F. Romaniello added a comment — 7th June 2011, 17:42:54:

i like what fabio said... +1.
but what would be the behaviour with one parameter?

---

Patrick Earl added a comment — 13th June 2011, 9:43:34:

Using a registered ilike function sounds good to me. StartsWith currently uses like IIRC.

[hazzik]

Depends on #944

[Clement-Jean]

Is it still open or the feature is done ? I would like to help

[fredericDelaporte]

No, it is not done.

I think no completely satisfactory solution can be done. In .Net, comparisons are case sensitive by default, but NHibernate has no way to know if the database will do a case sensitive match or a case insensitive one. This depends on the database settings.

In the case of SQL-Server by example, this can be set at the column level, database level or server level (through collation of column, default collation of database, or default collation of the server). A query can supply a collation on a string comparison, but NHibernate has no way to know which one should be used, since there are more than one case sensitive collation or case insensitive collation. And specifying a collation in a query can have averse effects, such as discarding indexes for the query and causing table scans instead.

The suggestion about the ilike in comments here only covers the case where the default comparison is case-sensitive and it is wished to do a case-insensitive one. ilike is currently a Criteria "trick" emulating case-insensitive comparison by, in most cases, lower-casing operands first. This may have issues (Turkish i issues notably). (In some cases, say PostgreSQL, it uses a built-in feature of the database handling this.)

In most cases, it is better to adjust the database, provided it offers such a possibility.