Search code examples
c#nhibernatenhibernate-mappinghbm

Why NHibernate uses NVARCHAR instead of VARCHAR ignoring column type mapping?

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

Solution

  • 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.