NHibernate: Is it possible for IUserType to convert a value to null and have it output as IS NULL in the resulting query?

Question

We are using NHibernate as our ORM. We have a table that one of the columns has a custom IUserType mapped onto it. The purpose of the IUserType is that depending on the type of connection we are using the value might be null or just an empty string.

So when we run this query depending on the underlying data storage the IUserType will convert the empty string to null Session.QueryOver<MyTable>().Where(t=>t.MyColumn == string.Empty).ToList() Witch creates this query select * from MyTable where MyColumn = NULL

Looking at the NHibernate code there is this code

SqlStringBuilder sqlBuilder = new SqlStringBuilder(4 * columnNames.Length);
var columnNullness = typedValue.Type.ToColumnNullness(typedValue.Value, criteriaQuery.Factory);

if (columnNullness.Length != columnNames.Length)
{
    throw new AssertionFailure("Column nullness length doesn't match number of columns.");
}

for (int i = 0; i < columnNames.Length; i++)
{
    if (i > 0)
    {
        sqlBuilder.Add(" and ");
    }

    if (columnNullness[i])
    {
        sqlBuilder.Add(columnNames[i])
                  .Add(Op)
                  .Add(parameters[i]);
    }
    else
    {
        sqlBuilder.Add(columnNames[i])
                  .Add(" is null ");
    }
}
return sqlBuilder.ToSqlString();

but column nullness is just based on the current value not the converted value this is from CustomType in NHibernate code

public override bool[] ToColumnNullness(object value, IMapping mapping)
{
    bool[] result = new bool[GetColumnSpan(mapping)];
    if (value != null)
        ArrayHelper.Fill(result, true);
    return result;
}

Is there anyway to have a IUserType convert a value to null and have the query come out as IS NULL?

Answer 1

Looks like a bug (missing feature) for IUserType types.

It seems in your case you can implement custom type based on AbstractStringType. Something like:

[Serializable]
public class EmptyAsDbNullStringType : AbstractStringType
{
    public EmptyAsDbNullStringType() : base(new StringSqlType())
    {
    }

    public EmptyAsDbNullStringType(int length) : base(new StringSqlType(length))
    {
    }

    public override string Name => "EmptyAsDbNullString";

    public override void NullSafeSet(DbCommand st, object value, int index, bool[] settable,
        ISessionImplementor session)
    {
        base.NullSafeSet(st, ToDbValue(value), index, settable, session);
    }

    public override object NullSafeGet(DbDataReader rs, string name, ISessionImplementor session)
    {
        return FromDbValue(base.NullSafeGet(rs, name, session));
    }

    public override bool[] ToColumnNullness(object value, IMapping mapping)
    {
        return base.ToColumnNullness(ToDbValue(value), mapping);
    }

    private static object ToDbValue(object value)
    {
        return string.Empty.Equals(value) ? null : value;
    }

    private static object FromDbValue(object value)
    {
        return value ?? string.Empty;
    }
}

You might need to override some more members to make it work properly in all cases.