Mapping empty strings to NULL in NHibernate

Question

I have a SQL Server DB with a recursive table:

MyTable:
 ID : string PrimaryKey
 Parent: string references MyTable - NOTNULL !!

and map with Fluent NHibernate to

class MyTable
{
  public virtual string ID {get; set;}
  public virtual MyTable Parent {get; set;}
}

My problem is that Parent should be null in my C# app if the column Parent is "" (empty string) in the database and vice versa. Unfortunately I can't change the column type to accept NULL!

I tried to use IEmptyInterceptor but I don't get it working.

Thanks in advance, forki

Answer 1

You need to have an IUserType for the primary key column, which does the special NULL value handling.

public MyTableMap()
{
    Id(x => x.EntryNo)
        // Since the PK is a string, it must be assigned by the application.
        .GeneratedBy.Assigned()
        .SetAttribute("type", typeof(SpecialNullValueStringType).AssemblyQualifiedName);

    References(x => x.Parent);
}

public class SpecialNullValueStringType : IUserType
{
    #region IUserType Members
    public bool IsMutable
    {
        get { return false; }
    }

    public Type ReturnedType
    {
        get { return typeof(string); }
    }

    public SqlType[] SqlTypes
    {
        get { return new[] { NHibernateUtil.String.SqlType }; }
    }

    public object NullSafeGet(IDataReader rs, string[] names, object owner)
    {
        var obj = NHibernateUtil.String.NullSafeGet(rs, names[0]);

        if (obj == null)
        {
            return null;
        }

        var value = (string) obj;
        if (String.IsNullOrEmpty(value))
        {
            return null;
        }

        return value;
    }

    public void NullSafeSet(IDbCommand cmd, object value, int index)
    {
        if (value == null)
        {
            ((IDataParameter) cmd.Parameters[index]).Value = String.Empty;
        }
        else
        {
            ((IDataParameter) cmd.Parameters[index]).Value = value;
        }
    }

    public object DeepCopy(object value)
    {
        return value;
    }

    public object Replace(object original, object target, object owner)
    {
        return original;
    }

    public object Assemble(object cached, object owner)
    {
        return cached;
    }

    public object Disassemble(object value)
    {
        return value;
    }

    public new bool Equals(object x, object y)
    {
        if (ReferenceEquals(x, y))
        {
            return true;
        }

        if (x == null || y == null)
        {
            return false;
        }

        return x.Equals(y);
    }

    public int GetHashCode(object x)
    {
        return x == null ? typeof(string).GetHashCode() + 473 : x.GetHashCode();
    }
    #endregion
}

Answer 2

I found a (messy) way to get this working:

public class NullEventListener : IPreUpdateEventListener, IPreInsertEventListener, IPreLoadEventListener
{
    #region IPreInsertEventListener Members

    public bool OnPreInsert(PreInsertEvent preInsertEvent)
    {
        var instance = preInsertEvent.Entity as MyTable;
        if (instance == null)
            return false;

        if (instance.Parent == null)
            Set(preInsertEvent.Persister, preInsertEvent.State, "Parent", string.Empty);     

        return false;
    }

    #endregion

    #region IPreLoadEventListener Members

    public void OnPreLoad(PreLoadEvent preLoadEvent)
    {
        var instance = preLoadEvent.Entity as MyTable;
        if (instance == null)
            return;

        try
        {
            // this is really messy!!
            var parent = Get(preLoadEvent.Persister, preLoadEvent.State, "Parent") as MyTable;
            if (parent == null || parent.ID == "")
                throw new Exception("Set to null");
        }
        catch (Exception)
        {
            Set(preLoadEvent.Persister, preLoadEvent.State, "Parent", null);
        }

        return;
    }

    #endregion

    #region IPreUpdateEventListener Members

    public bool OnPreUpdate(PreUpdateEvent preUpdateEvent)
    {
        var instance = preUpdateEvent.Entity as MyTable;
        if (instance == null)
            return false;

        if (instance.Parent == null)
            Set(preUpdateEvent.Persister, preUpdateEvent.State, "Parent", string.Empty);     

        return false;
    }

    #endregion

    private static void Set(IEntityPersister persister, object[] state, string propertyName, object value)
    {
        int index = Array.IndexOf(persister.PropertyNames, propertyName);
        if (index == -1)
            return;
        state[index] = value;
    }

    private static object Get(IEntityPersister persister, object[] state, string propertyName)
    {
        int index = Array.IndexOf(persister.PropertyNames, propertyName);
        if (index == -1)
            return null;
        return state[index];
    }
}

Thanks and regards, forki

Answer 3

Have you considered using the Null Object Pattern instead?

Answer 4

I tried to implement IUserType for my mapping:

public class MyCustomString : IUserType
{
    public Type ReturnedType
    {
        get { return typeof (MyTable); }
    }

    public SqlType[] SqlTypes
    {
        get { return new[] {NHibernateUtil.String.SqlType}; }
    }    

    public object NullSafeGet(IDataReader rs, string[] names, object owner)
    {
        object obj = NHibernateUtil.String.NullSafeGet(rs, names[0]);

        if (obj == null) return null;

        var s = (string) obj;

        if (s == "")
            return null;
        using (ISession session = SessionHelper.OpenSession())
        {
            using (session.BeginTransaction())
            {
                return MyTable.Get(session, s);
            }
        }
    }

    public void NullSafeSet(IDbCommand cmd, object value, int index)
    {
        ((IDataParameter) cmd.Parameters[index]).Value = value == null ? 0 : ((MyTable) value).EntryNo;
    }

   ...
}

and changed the mapping to

    public MyTableMap()
    {
        Id(x => x.EntryNo);

        Map(x => x.Parent).CustomTypeIs<MyCustomString>();

        // References() doesn't allow CustomTypeIs()
        // References(x => x.Parent).CustomTypeIs<MyCustomString>();
    }

This seems to work for my root - but it always opens a session to get the right parent. And it is not lazy - so it always retrieves all parents up to the root :-(

This can't be the right way. I don't want to open a new session - but otherwise I am returning a string and get a runtime type error.

Answer 5

I'd go for a IUserType which would convert empty string to nulls and vice versa. Two methods to pay attention to are NullSafeGet and NullSafeSet.

Not sure though how custom types integrate with Fluent NHibernate.