NHibernate truncating values when using Restrictions with a “Like”

Question

I have a system that uses Fluent NHibernate auto mapping. I have a string property that is mapped with a length of 3.

mapping.Map(x => x.CCY1Code).Length(3);

Here is an example of a restriction that I'm building:

Restrictions.Like("CCY1Code", "USD", MatchMode.Anywhere);

Watching this in SQL Server Profiler, it looks like NHibernate is parameterizing the SQL. But in doing so it is limiting it to three characters. But NHibernate is also surrounding my value "USD" with percent signs. So what should be "%USD%" gets truncated to "%US". Here are some snippets from SQL Server Profiler:

CCY1Code like @p9
@p9 nvarchar(3)
@p9=N'%US'

Other than lengthening my field to five characters to work around this, is there any other way to address this problem?

Jaguar Jaguar · Accepted Answer · 2012-01-17T13:10:06

I would say that this is partially a bug.

It is pointless to search with Like on a 3 character field when the token itself is 3 characters long. Use an Equals instead so you will take advantage of indexes (if any are setup).

You will fall on the bug though when searching for '%US%', but a workaround would be to run the query as:

Restrictions.Like("CCY1Code", "US", MatchMode.Start) 
      || Restrictions.Like("CCY1Code", "US", MatchMode.End)

It's better because the first part also takes into advantage of Indexes while avoiding truncation

EDIT: I would just like to add here that this is definitely a bug from NHibernate's part since you can also Like using various expressions as described in http://msdn.microsoft.com/en-us/library/ms179859.aspx

NHibernate truncating values when using Restrictions with a “Like”

1 Answers