How to map a VARCHAR(MAX) column in NHibernate hbm.xml mapping files

Question

We have a SQL Server table with varchar and nvarchar max columns like this:

CREATE TABLE [dbo].[MyTable](
  :
  [MyBigUnicodeColumn] [nvarchar](max) NULL,
  [MyBigAnsiColumn]    [varchar](max) NULL,
  :

When creating the mapping (hbm.xml) files, the documentation says to use StringClob as the type attribute for large objects with a database type of DbType.String, but it doesn't say what to do if the database type is DbType.AnsiString.

<class name="MyTable" table="MyTable" lazy="false">
  :
  <property name="MyBigUnicodeColumn" type="StringClob" />
  <property name="MyBigAnsiColumn" type="????" />
  :

This is for NHibernate 3.3.1.

Answer 1

You can map them just as string or AnsiString.

<property name="MyBigUnicodeColumn" type="string" length="1000000"/>
<property name="MyBigAnsiColumn" type="AnsiString" length="1000000" />

Whenever the length is larger then 4000 or 8000 respectively, NH creates an nvarchar(max) or varchar(max).

I may be that the length is used for sql parameters and that it is truncated to the specified length (it depends on the NH version you are using, there had been some changes). So better specify it large enough.

---

Edit: Unfortunately, it doesn't work with the AnsiString the same as with normal strings. I read some NH code and found the following:

varchar(max) is supported by the dialect from SQL Server 2005 on.

MsSql2000Dialect.cs, line 205

RegisterColumnType(DbType.AnsiString, SqlClientDriver.MaxSizeForLengthLimitedAnsiString, "VARCHAR($l)");

MsSql2005Dialect.cs, line 19:

RegisterColumnType(DbType.AnsiString, SqlClientDriver.MaxSizeForAnsiClob, "VARCHAR(MAX)");

It registers varchar(max) as the sql type to choose when an AnsiString is mapped larger then 8000.

In the SqlClientDriver.cs you can see that it implements "blobs" in the params for strings, but not for ansi strings (line 135):

case DbType.AnsiString:
case DbType.AnsiStringFixedLength:
    dbParam.Size = MaxSizeForLengthLimitedAnsiString;
    break;
// later on
case DbType.String:
case DbType.StringFixedLength:
    dbParam.Size = IsText(dbParam, sqlType) ? MaxSizeForClob : MaxSizeForLengthLimitedString;
    break;

It always puts 8000 as the limit of the parameter of type AnsiString.

Because of the inconsistency between the driver and the dialect, I would call it a bug.

Because the bug happens on all AnsiStrings, it doesn't help to specify the sql-type in the mapping (NH is able to choose the correct sql type). You need to use the workaround proposed in the thread you started on the NH forum:

<property name="MyBigAnsiColumn" type="StringClob" sql-type="VARCHAR(max)" /> 

---

I reported it as a bug: https://nhibernate.jira.com/browse/NH-3252