SQL 2008 HierarchyID support in NHibernate

Question

Searched various NHibernate lists and haven't come up with a definitive answer. The SQL2008 dialect doesn't appear to have support for the HierarchyID data type - new date and time types only.

Does anyone have a good implementation or an effective workaround? I'd really like to leverage HierarchyID in a new app of mine. Support for this interesting and powerful data type is sorely lacking in MS's own tools so I'm not shocked that NHibernate doesn't have support.

There are some approaches out there that I haven't delved into yet. Wondering if anyone has some experience in what works, what is more performant, etc.'

Full disclosure: I'm working with Castle ActiveRecord but this seems like an NHibernate issue.

Answer 1

I've given Needles' answer a test run. It's a very good answer but there are some changes needed to make it function (at least in .NET 4). Here's what I've come up with for my project:

Update: the following code can be download over at GitHub and will be updated there. NHiberntate.HierarchyId.UserType

SqlHierarchyId IUserType

namespace NHibernate.UserTypes
{
    using SqlTypes;
    using System;
    using System.Data;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Types;

    public class HierarchyId : IUserType
    {
        #region Properties

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

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

        public bool IsMutable
        {
            get { return true; }
        }

        #endregion Properties

        #region Methods

        new public 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.GetHashCode();
        }

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

            if (prop1 == null) return null;

            return SqlHierarchyId.Parse(new SqlString(prop1.ToString()));
        }

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

            else if (value is SqlHierarchyId)
                ((IDataParameter)cmd.Parameters[index]).Value = ((SqlHierarchyId)value).ToString();
        }

        public object DeepCopy(object value)
        {
            if (value == null) return null;

            return SqlHierarchyId.Parse(((SqlHierarchyId)value).ToString());
        }

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

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

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

        #endregion Methods
    }
}

Mapping

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="DataLayer" namespace="NHibernate.Map">
    <class name="NHibernate.Map.OrganizationUnit, DataLayer" table="`orgunit`">

        <property name="HierarchyId" column="`ou_hid`" type="NHibernate.UserTypes.HierarchyId, DataLayer" />
        ...

    </class>
</hibernate-mapping>

Object with HierarchyId

namespace NHibernate.Map
{
    using Microsoft.SqlServer.Types;

    public class OrganizationUnit
    {
        #region Fields

        private SqlHierarchyId _hierarchyId;
        ...

        #endregion Fields

        #region Properties

        public virtual SqlHierarchyId HierarchyId
        {
            get { return _hierarchyId; }
            set { _hierarchyId = value; }
        }
        ...

        #endregion Properties
    }
}

Answer 2

Disclaimer: Im not an NHibernate expert, however, we are using it with Fluent in an upcoming project which uses SQL Server 2008 R2 and Hierarchy IDs. The code below is what we are using currently on our dev environment and is not fully tested/refined. I copied the majority of the code from elsewhere (sorry I lost the link!)

You need to create a User Defined Type and then use it in your mappings. The mapping below is Fluent, Im not aware how to do it using ActiveRecord but Im guessing it should be similar!

User Defined Type

namespace YourNamespace {
    public class SqlHierarchyIdUserType : IUserType {
    public 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.GetHashCode();
    }

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

        if(prop1 == null)
            return null;

        return SqlHierarchyId.Parse(new SqlString(prop1.ToString()));
    }

    public void NullSafeSet(IDbCommand cmd, object value, int index) {
        if(value == null) {
            ((IDataParameter)cmd.Parameters[index]).Value = DBNull.Value;
        } else {
            if(value is SqlHierarchyId) {
                SqlHierarchyId hId = (SqlHierarchyId)value;
                ((IDataParameter)cmd.Parameters[index]).Value = hId.ToString();
            }
        }
    }

    public object DeepCopy(object value) {
        if(value == null)
            return null;

        var sourceTarget = (SqlHierarchyId)value;
        SqlHierarchyId copy = SqlHierarchyId.Parse(sourceTarget.ToString());

        return copy;

    }

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

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

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

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

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

    public bool IsMutable {
        get { return true; }
    }
}
}

Fluent Mapping

Map(e => e.YourSqlHierarchyIdProperty)
    .Column("YourSqlHierarchyIdFieldName")
    .CustomType<SqlHierarchyIdUserType>();

Reading this post:

Castle ActiveRecord: Map to IUserType wihtin Class in C#

ActiveRecord uses a [Property] attribute to map User Defined Types. So for you it would look something like this:

public class YourDataObject {
    [Property(ColumnType="YourNamespace.SqlHierarchyIdUserType, YourNamespace")
    public virtual SqlHierarchyId YourSqlHierarchyIdProperty;
}

Hope it helps!