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     } }