search

nhibernate / nhibernate-core

NHibernate Object Relational Mapper
https://nhibernate.info
GNU Lesser General Public License v2.1
2.13k stars 928 forks source link

NH-2099 - Linq Expressions and case sensitivity #933

Open nhibernate-bot opened 7 years ago

nhibernate-bot commented 7 years ago

Jose F. Romaniello created an issue — :

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 — : This would be great to have!. In the meantime ... is there a workaround?

Patrick Earl added a comment — : 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 — : 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 — : 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 — : To answer my previous question, a quick search for "collation" leads to no relevant results.

Mattias Öhrn added a comment — : 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 — : 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 — : i like what fabio said... +1. but what would be the behaviour with one parameter?

Patrick Earl added a comment — : Using a registered ilike function sounds good to me. StartsWith currently uses like IIRC.

hazzik commented 6 years ago

Depends on #944

Clement-Jean commented 6 years ago

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

fredericDelaporte commented 6 years ago

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.