Wednesday, February 9, 2011

NHibernate Criteria using substring SqlFunction projection with in clause

I was playing with new NHibernate release (3.0.0 GA) and all of a sudden I felt an urge to check if one of bugs I have stumbled upon in 2.2 version is fixed. A quick example and few moments after - O happy day!!! This bug is fixed in 3.0.0 GA version.

Thank you awesome NHibernate developers.

P.S. Source code
using System;
using System.Collections.Generic;
using System.Reflection;

using NHibernate;
using NHibernate.Cfg;
using NH3Tests.SimpleModel;
using NHibernate.Criterion;

namespace NH3Tests
{
    public class Program
    {
        static ISessionFactory factory;

        public static void Main(string[] args)
        {
            log4net.Config.XmlConfigurator.Configure();

            IList<Room> rooms = null;
            using (ISession session = OpenSession())
            {
                ICriteria query = session.CreateCriteria<Room>l;()
                    .Add(Expression.In(
                        Projections.SqlFunction("substring",
                            NHibernateUtil.String,
                            Projections.Property("Code"),
                            Projections.Constant(1),
                            Projections.Constant(2)),
                        new string[] { "A1", "A2" }));

                rooms = query.List<Room>();
            }

            Console.WriteLine("Done.");
        }

        public static ISession OpenSession()
        {
            if (factory == null)
            {
                Configuration c = new Configuration();
                c.AddAssembly(Assembly.GetCallingAssembly());
                factory = c.BuildSessionFactory();
            }
            return factory.OpenSession();
        }
    }
}

POCO and mapping file:

using System;
using System.Collections.Generic;

namespace NH3Tests.SimpleModel
{
    public class Room
    {
        private int _id = 0;
        private string _code = null;
        private string _description = null;

        public Room()
        { 
        }

        public int Id
        {
            get { return _id; }
            set { _id = value; }
        }

        public string Code
        {
            get { return _code; }
            set { _code = value; }
        }

        public string Description
        {
            get { return _description; }
            set { _description = value; }
        }
    }
}
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true">
  <class name="NH3Tests.SimpleModel.Room, NH3Tests" table="room" lazy="false">
    <id name="Id" access="field.camelcase-underscore"  column="room_id">
      <generator class="native" />
    </id>
    <property name="Code" access="field.camelcase-underscore"  column="code"/>
    <property name="Description" access="field.camelcase-underscore" column="description"/>
  </class>
</hibernate-mapping>

And config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="hibernate-configuration"
    type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate" />
    <section name="log4net"
    type="log4net.Config.Log4NetConfigurationSectionHandler,log4net" />
  </configSections>
  <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
    <session-factory>
      <property name="connection.provider">
        NHibernate.Connection.DriverConnectionProvider
      </property>
      <property name="connection.driver_class">
        NHibernate.Driver.SqlClientDriver
      </property>
      <property name="connection.connection_string">
        Server=(local);database=NH3Tests;Integrated Security=SSPI;
      </property>
      <property name="dialect">
         NHibernate.Dialect.MsSql2008Dialect
      </property>
      <property name="show_sql">true</property>
      <property name='proxyfactory.factory_class'>NHibernate.ByteCode.LinFu.ProxyFactoryFactory, NHibernate.ByteCode.LinFu</property>
    </session-factory>
  </hibernate-configuration>
   <log4net>
    <appender name="ConsoleAppender"
    type="log4net.Appender.ConsoleAppender, log4net">
      <layout type="log4net.Layout.PatternLayout, log4net">
        <param name="ConversionPattern" value="%m\n" />
      </layout>
    </appender>
    <root>
      <priority value="INFO" />
      <appender-ref ref="ConsoleAppender" />
    </root>
  </log4net>
</configuration>

And room table:
CREATE TABLE [dbo].[room](
 [room_id] [int] IDENTITY(1,1) NOT NULL,
 [code] [nvarchar](16) NOT NULL,
 [description] [nvarchar](256) NULL,
 CONSTRAINT [PK_room] PRIMARY KEY CLUSTERED 
(
 [room_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]