Substring and an In clause with NH Projections

This is something I’ve stumbled upon and couldn’t really find where did it all went wrong.

I have a Where clause containing a substring and an In clause. In T-Sql it looks like this:

 SELECT * FROM Employee WHERE SUBSTRING(Code, 1, 2) In ('one', 'two', 'three', 'four', 'five');

I’ve been trying to do this in NHibernate using ICriteria & Projections and came up with this code:

ICriteria criteria = session.CreateCriteria(typeof (Employee));

IProjection postCodeSubstring = Projections.SqlFunction
 ("substring", NHibernateUtil.String, Projections.Property
 Projections.Constant(1), Projections.Constant(2));

List<string> deptsIds = new List<string> {"one", "two", "three",
 "four", "five"};
 criteria.Add(Restrictions.In(postCodeSubstring, deptsIds));

IList<Employee> employees = criteria.List<Employee>();

However, this code generates some faulty t-sql:

SELECT this_.Id as Id0_0_, this_.FirstName as FirstName0_0_, this_.LastName as LastName0_0_
FROM [Employee] this_ WHERE substring(this_.FirstName, @p0, @p1)
in (@p2, @p3, @p4, @p5, @p6);
@p0 = 1, @p1 = 2, @p2 = 1, @p3 = 2, @p4 = 'one', @p5 = 'two', @p6 = 'three', @p7 = 'four', @p8 = 'five'

As you can see, the first two parameters of the In clause are the ints 1 and 2, instead of the varchars ‘one’ and ‘two’. On top of that 2 supplementary parameters were generated (@p7 = ‘four’, @p8 = ‘five’).

For the time being, the only way I found to resolve this issue is a workaround- mapping another property containing a substring formula. My FluentNH map contains these two lines now:

Map(x => x.FirstName);

Map(x => x.SubName).Formula("substring(FirstName, 1, 2)");

And the criteria would be:

ICriteria criteria = session.CreateCriteria(typeof (Employee));</pre>
List<string> deptsIds = new List<string> {"one", "two", "three", "four", "five"};
criteria.Add(Restrictions.In("SubName", deptsIds));

IList<Employee> employees = criteria.List<Employee>();

If I figure out what went wrong there with the projections, I’ll let the world know :).

Explore posts in the same categories: nhibernate


You can comment below, or link to this permanent URL from your own site.

2 Comments on “Substring and an In clause with NH Projections”

  1. Oskar Berggren Says:

    The upcoming 3.3.0 release contains a fix for.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: