SQL Server specific types support for OrmLite

Question

I just learned about a genius type that would simplify a lot of my work but it looks like my preferred ORM does not recognize it.

Is there a workaround to let ServiceStack OrmLite recognize HierarchyId in SQL Server? Any suggestions about which files to modify and any hints how to proceed?

EDIT :

Here is a better illustration of the problem. I have the following class:

public class MyClass
{
    public int Id { get; set; }
    public SqlHierarchyId HierarchyId { get; set; }
}

SqlHierarchyId is a custom SQL Server data type. OrmLite will generate the following class for it:

Funny enough, I can use the [StringLength(255)] attribute on the property and it will get the varchar(255) type instead:

I manually changed the table here and added the column data type to showcase the difference. Please note the data type of the third column:

Having a varchar representation is perfectly fine with other DBMS as it can be converted within C#, but with SQL Server it is preferable to have it match the corresponding data type. This will make the creation of views easier (due to the built-in functions of the hierarchyid data type).

I know the type is not supported by EF4 (not sure about 5). I also browsed the OrmLiteDialectProviderBase.cs file on GitHub and I can see a list of supported ADO.NET data types.

My simple question is: Is this a strong limitation by ADO.NET or this can be seen sometime in OrmLite? I am willing to help extending this part if any suggestions are made.

Primateship

Can you show a SQL + code-example of something you would like OrmLite to support? – Messily

Not sure what OrmLite does, but it looks like that should match to a string type, since it contains data like /123/234/125. – Scurvy

@mythz: Please have a look at the edited question – Primateship

@MoslemBenDhaou you may be able to use the new Custom Field Declarations to do what you want. Otherwise you can add a feature request for it but it's unlikely specialized RDBMS support will be a popular feature request so will have a low priority. – Messily

@mythz: nice approach thanks! I will try it and let you know. – Primateship

Isn't OrmLite something for java? Have you looked at DapperORM for C#? en.wikipedia.org/wiki/Dapper_ORM – Victoria

@Roland: There is two OrmLite, This is the ServiceStack one. It is built on top of Dapper – Primateship

Not trying to be a buzz kill, but I would avoid HierarchyId. I was looking at using HierarchyId at one point for one my my applications, and I was disappointed after seeing how horrible the performance can be. Check out this presentation by Louis Davidson, it has comprehensive performance comparisons for different hierarchy setups (Slide 44 in particular). – Stereoscopic

Answer 1

ADO.NET has support for the hierarchyid type, an example can be found here and shows ADO.NET can read values from Sql Server as a hierarchyid directly but you need to pass parameters to the server as a string.

Adding support for the hierarchyid type methods to a ORM framework would break the abstraction between the ORM API and the RDMS. I would assume this is the reason such functionality has not been added to Entity Framework.

You could work around the issue by keeping a string representation of the hierarchy in your database and having the hierarchyid version as a computed property in both your database and your C# class, you would need to exclude the computed C# property from the ORM mapping.

For example your table column would be declared as:

[SqlHierarchyId] AS ([hierarchyid]::Parse([HierarchyId])) PERSISTED 

and your class as:

public class MyClass {

    public string HierarchyId {
        get;
        set;
    }

    [Ignore]
    public SqlHierarchyId SqlHierarchyId {
        get {
            return SqlHierarchyId.Parse(new SqlString(HierarchyId));
        }
        set {
            HierarchyId = value.ToString();
        }
    }

}

This will persisted updates from the .Net layer and allow you to use the hierarchyid methods to construct queries in SQL Server and work with materialised objects in the .Net layer.

You would have to construct queries against the string representation in you ORM layer but this could still leverage some of the hierarchyid helper methods, for example:

public IEnumerable<MyClass> GetDescendants(MyClass node) {

    string currentLocation = node.HierarchyId;
    string followingSibling 
        = node.SqlHierarchyId.GetAncestor(1)
              .GetDescendant(node.SqlHierarchyId, SqlHierarchyId.Null)
              .ToString();

    return db.Select<MyClass>(n => n.HierarchyId > currentLocation 
                                && n.HierarchyId < followingSibling);

}

Aplogies if I have got the ORMLite syntax wrong.

Varices