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

01.using System;

02.using System.Collections.Generic;

03.using System.Reflection;

04.

05.using NHibernate;

06.using NHibernate.Cfg;

07.using NH3Tests.SimpleModel;

08.using NHibernate.Criterion;

09.

10.namespace NH3Tests

11.{

12.public class Program

13.{

14.static ISessionFactory factory;

15.

16.public static void Main(string[] args)

17.{

18.log4net.Config.XmlConfigurator.Configure();

19.

20.IList rooms = null;

21.using (ISession session = OpenSession())

22.{

23.ICriteria query = session.CreateCriterial;()

24..Add(Expression.In(

25.Projections.SqlFunction("substring",

26.NHibernateUtil.String,

27.Projections.Property("Code"),

28.Projections.Constant(1),

29.Projections.Constant(2)),

30.new string[] { "A1", "A2" }));

31.

32.rooms = query.List();

33.}

34.

35.Console.WriteLine("Done.");

36.}

37.

38.public static ISession OpenSession()

39.{

40.if (factory == null)

41.{

42.Configuration c = new Configuration();

43.c.AddAssembly(Assembly.GetCallingAssembly());

44.factory = c.BuildSessionFactory();

45.}

46.return factory.OpenSession();

47.}

48.}

49.}

POCO and mapping file:

01.using System;

02.using System.Collections.Generic;

03.

04.namespace NH3Tests.SimpleModel

05.{

06.public class Room

07.{

08.private int _id = 0;

09.private string _code = null;

10.private string _description = null;

11.

12.public Room()

13.{

14.}

15.

16.public int Id

17.{

18.get { return _id; }

19.set { _id = value; }

20.}

21.

22.public string Code

23.{

24.get { return _code; }

25.set { _code = value; }

26.}

27.

28.public string Description

29.{

30.get { return _description; }

31.set { _description = value; }

32.}

33.}

34.}

01.xml version="1.0" encoding="utf-8" ?>

02.<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true">

03.<class name="NH3Tests.SimpleModel.Room, NH3Tests" table="room" lazy="false">

04.<id name="Id" access="field.camelcase-underscore" column="room_id">

05.<generator class="native" />

06.id>

07.<property name="Code" access="field.camelcase-underscore" column="code"/>

08.<property name="Description" access="field.camelcase-underscore" column="description"/>

09.class>

10.hibernate-mapping>

And config:

01.xml version="1.0" encoding="utf-8" ?>

02.<configuration>

03.<configSections>

04.<section name="hibernate-configuration"

05.type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate" />

06.<section name="log4net"

07.type="log4net.Config.Log4NetConfigurationSectionHandler,log4net" />

08.configSections>

09.<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">

10.<session-factory>

11.<property name="connection.provider">

12.NHibernate.Connection.DriverConnectionProvider

13.property>

14.<property name="connection.driver_class">

15.NHibernate.Driver.SqlClientDriver

16.property>

17.<property name="connection.connection_string">

18.Server=(local);database=NH3Tests;Integrated Security=SSPI;

19.property>

20.<property name="dialect">

21.NHibernate.Dialect.MsSql2008Dialect

22.property>

23.<property name="show_sql">trueproperty>

24.<property name='proxyfactory.factory_class'>NHibernate.ByteCode.LinFu.ProxyFactoryFactory, NHibernate.ByteCode.LinFuproperty>

25.session-factory>

26.hibernate-configuration>

27.<log4net>

28.<appender name="ConsoleAppender"

29.type="log4net.Appender.ConsoleAppender, log4net">

30.<layout type="log4net.Layout.PatternLayout, log4net">

31.<param name="ConversionPattern" value="%m\n" />

32.layout>

33.appender>

34.<root>

35.<priority value="INFO" />

36.<appender-ref ref="ConsoleAppender" />

37.root>

38.log4net>

39.configuration>

And room table:

01.CREATE TABLE [dbo].[room](

02.[room_id] [int] IDENTITY(1,1) NOT NULL,

03.[code] [nvarchar](16) NOT NULL,

04.[description] [nvarchar](256) NULL,

05.CONSTRAINT [PK_room] PRIMARY KEY CLUSTERED

06.(

07.[room_id] ASC

08.)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

09.) ON [PRIMARY]

Can foreign key constraints be temporarily disabled using T-SQLin SQL Server.

I find it useful when populating data from one database to another. It is much better approach than dropping constraints. As you mentioned it comes handy when dropping all the data in the database and repopulating it (say in test environment).

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

To switch them back on, run: (the print is optional of course and it is just listing the tables

- enable all constraints
exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Also sometimes it is handy to disable all triggers as well,

sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"

exec sp_msforeachtable @command1="print '?'",
@command2="ALTER TABLE ? ENABLE TRIGGER  all"

NHibernate and ‘Invalid Index N for this SqlParameterCollection with Count=N error’

If you have ever used NHibernate I am sure you have encounted the error ‘Invalid Index ‘N’ for this SqlParameterCollection with Count=N’ (where N is any number) exception when trying to create your mappings.

If you are completely new to NHibernate, as I am I am sure this one has you scratching your head saying…. WTF.

The good news is that ‘normally’ the solution to this problem is pretty easy, but before I tell you the answer let me explain the issue.

Take a look at the code below

view plaincopy to clipboardprint?

1. public EpisodeMap()
2. {
3. WithTable( "Episode" );
5. Id( x => x.ID ).GeneratedBy.Identity();
7. Map( x => x.LevelTypeID );
8. Map( x => x.Name );
9. Map( x => x.Description );
10. Map( x => x.EpisodeNumber );
11. Map( x => x.EpisodeDate );
12. Map( x => x.CreatedDate );
13. Map( x => x.Enabled );
15. References( x => x.EpisodeLevel )
16. .WithForeignKey( "LevelTypeID" ).TheColumnNameIs( "LevelTypeID" )
17. .Access.AsCamelCaseField( Prefix.Underscore )
18. .FetchType.Join();
20. }

Take notice to the mapping above. I have a many-to-one mapping for EpisodeLevel, but I have also created and mapped the FK to EpisodeLevel as LevelTypeID.

The issue (as i have experienced it) is this:

Because I have mapped my FK to the Episode Level table as .LevelTypeID as well as to the EpisodeLevel entity NH is going to try to create multiple associations on that field. However it cannot because that is not correct.

To solve this issue (mostly in my experience) all you need to do is remove the following line
Map( x => x.LevelTypeID );

If you MUST populate the LevelTypeID property at the top level, do so by providing the value as a pass through from Episode Level as such:

view plaincopy to clipboardprint?

1. public Int32 LevelTypeID
2. {
3. get { return EpisodeLevel.LevelTypeID; }
4. }

NHibernate and ‘Invalid Index N for this SqlParameterCollection with Count=N error’ is error related to NHibernate mapping so please check your mapping class mostly with composite keys and foreign keys.

I hope this helps someone.

Thanks,

Rajesh