2

When I run the following code:

List<Dag> result = session.CreateCriteria<Dag>()
            .Add(Expression.Eq("Object_id", pObjectId))
            .List<Dag>().ToList();
}

NHibernate generates the following SQL query:

exec sp_executesql N'SELECT this_. ... FROM schm.dag this_ WHERE this_.object_id = @p0',N'@p0 nvarchar(4000)',@p0=N'...'

The problem here is the CAST to nvarchar(4000). The hbm.xml file defines the mapping for the object_id column as:

<property name="Object_id" type="String">
  <column name="object_id" not-null="false" length="40" sql-type="varchar" />
</property>

So why NHibernate is ignoring the information in the mapping file and not using varchar(40)? I could not find a way of explicitly stating the property type used for the criteria. I'm not even sure if I'd need it, the mapping is in the hbm.xml file, why won't NHibernate pick it up?

This is NHibernate 4.1.4000 running on .Net Framework 4.6 (legacy app).

Amit Joshi
  • 15,448
  • 21
  • 77
  • 141
GrumpyRodriguez
  • 649
  • 5
  • 12

1 Answers1

1

While mapping the property in your code:

<property name="Object_id" type="String">

you are specifying type as String. Change it to AnsiString instead.

Please refer to NHibernate Documentation and search for "Table 5.3. System.ValueType Mapping Types" in there for list of all types.

Please note that using NVARCHAR (and hence string) is better way to go in many situations considering unicode support.

Amit Joshi
  • 15,448
  • 21
  • 77
  • 141
  • Thanks a lot Amit. This was indeed the case. Nhibernate is now using varchar(8000) – GrumpyRodriguez Nov 10 '21 at 10:23
  • 1
    In case someone is wondering why nvarchar is preferable when storing String values it's because the nvarchar column type has support for unicode and will give you correct results when using string functions such as Len(), Reverse(), etc. – andrerav Feb 13 '22 at 10:24