sql server - Is there a practical way to use the hierarchyID datatype in entity framework 4?

Question

As it stands now, the CLR UDTs including HierarchyID aren't supported in Entity Framework 4. HierarchyID.ToString() is useful, but breaks down once any item has 10+ siblings (the basic structure is /3/4/12/ or /3/4/2/ so the 12th node will sort before the 2nd node).

A little more about potential options:

• Bring back hierarchyID as a varbinary and implement my own binary sorter

• Bring back hierarchyID as a varbinary and implement my own hierarchyID.ToString() method which pads the numbers with zeros while building the string so the resulting string is sortable (i.e. "/0003/0004/0012/"). I disassebled Microsoft.SqlServer.Types.dll and looked at the implementation. It looks like the interals are based of a class called "OrdTree" and I could use that class as a basis for re-implementation.

• Write my own CLR type for SQL to work on the binary data and build its own string (a variation of option 2). Although, comes with an added deployment headache.

• Write a SQL udf to parse the hierarchy string and pad it on the DB layer. The lack of array processing/regex's seems like the biggest issue here.

• Sort by hierarchyID on the database layer and use the ROW_NUMBER() function as a stand in for sort order.

• Write some helper methods on the .net layer which re-parse the hierarchyId.ToString() and generate a sortable string (i.e. "/0003/0004/0012/").

So my question is has anyone worked around the limitation? Did you use any of the above strategies? If so, how?

Answer 1

Well, I seem to be getting views but no responses. I had some immediate needs to work with the hierarchy structure above SQL, so i put together a static helper class. I don't consider this a complete solution, but so far it works relatively well. PadPath is really the critical function here.

public static class SQLHierarchyManipulatin {
    const int   DEFAULT_PAD_LEN     = 3;
    const char  DEFAULT_PAD_CHAR    = '0';

    public static string PadPath(string Hierarchy) {
        return PadPath (Hierarchy, DEFAULT_PAD_LEN);
    }       
    public static string PadPath(string Hierarchy, int padLen) {
        string[]    components  = Hierarchy.Split('/');

        for (var i = 0; i < components.Length; i++ ) {
            if (components[i] != "") {
                components[i] = components[i].PadLeft(padLen, DEFAULT_PAD_CHAR);
            }
        }
        return string.Join("/", components);
    }

    public static int CurrentNodeIndex(string Hierarchy) {
        string[]    components  = Hierarchy.Split('/');
        string      startItem   = components[components.Length - 2]; //one slot back from trailing slash

        return int.Parse(startItem);
    }

    public static string ParentPath (string Hierarchy) {
        return  Hierarchy.Substring(0, Hierarchy.TrimEnd('/').LastIndexOf('/') + 1);
    }

    public static string AppendChildWithPadding (string Hierarchy, int childIndex, int padLen) {
        return AppendChild(Hierarchy, childIndex, DEFAULT_PAD_LEN);
    }
    public static string AppendChildWithPadding (string Hierarchy, int childIndex) {
        return AppendChild(Hierarchy, childIndex, DEFAULT_PAD_LEN);
    }
    public static string AppendChild (string Hierarchy, int childIndex) {
        return AppendChild(Hierarchy, childIndex, DEFAULT_PAD_LEN);
    }
    public static string AppendChild (string Hierarchy, int childIndex, int padLen) {
        return Hierarchy + childIndex.ToString().PadLeft(padLen, DEFAULT_PAD_CHAR) + "/";
    }
}

Hope this helps someone! Although, I'd still like to hear from people.