NHibernate extend restiction

Question

I need extend Like restriction to support accent insensitive, but I don't know where tinkering. Anyone know how I do this? I can't change collate in all customer databases and I'm using criteria and can't change this because I create a infrastructure that wrap criteria in my domain.

score 0 · Answer 1 · edited May 23 '17 at 12:12

Approach 1

You can do this with an SQL expression, i.e.

Expression.Sql(
      "your_field COLLATE Latin1_general_CI_AI LIKE ? COLLATE Latin1_general_CI_AI", 
      String.Format("%{0}%", your_field),
      NHibernateUtil.String)

Please see my answer to a similar question for further details. Also please see the answer to this question for details on the correct SQL format.

This, of course, is limited to a specific DBMS, in the example case SQL Server.

Approach 2

Another approach is to sub-class the NHibernate dialect for each DBMS the system will be used with, and add a custom function to each of these custom dialects. For example,

public class CustomMsSqlDialect : MsSql2005Dialect
{
  public CustomMsSqlDialect()
  {
      RegisterFunction("accentinsensitivelike",
        new SQLFunctionTemplate(NHibernateUtil.String,
            "?1 COLLATE Latin1_general_CI_AI like ?2 collate Latin1_general_CI_AI"));
  }
}

Then to call it use something like

var user = session.CreateQuery("from User u 
                                where accentinsensitivelike(u.name, :name)")
                  .SetParameter("name", name)
                  .UniqueResult<User>();

This has the advantage of working across multiple DBMS but is quite a bit more work than the first approach.

For further details please the following

http://blog.schuager.com/2009/06/case-sensitive-queries-in-nhibernate.html

Can I customize collation of query results in nHibernate?

Hope this helps.

@DiegoDias I have updated my answer with another possible solution. — mickfold, Apr 30 '13 at 19:35
I preferred to create a custom criteria to resolve my problem. I check the dialect and then I create a custom query expression. — Diego Dias, May 02 '13 at 13:59
@DiegoDias it would be good if you add an answer with your solution. — mickfold, May 03 '13 at 11:58

score 0 · Answer 2 · answered May 04 '13 at 05:02

0

I resolved my problem creating a custom criterion where I check dialect to identify what's the RDBMS that NHibernate are using, and then I create custom expression to it.

My code is here: source code

answered May 04 '13 at 05:02

Diego Dias

904
3
15
23

NHibernate extend restiction

2 Answers2

Approach 1

Approach 2