Snowing Code

Personal notes on software development

Substring and an In clause with NH Projections

(Publish date: 10/01/2010)

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 :) .

Tags: nhibernate
blog comments powered by Disqus