How could I serialize hierarchyid in Microsoft.AspNetCore.OData 8.x

Question

Using Microsoft.AspNetCore.OData 8.2.0, I'm trying to return a class with a HierarhyId property

public class Account
{
    [Key]
    public int Id { get; set; }
    public HierarchyId Level { get; set; } = null!;
    ...
}

I added the support for OData, the controller, the EDM configuration and all works well except the Level property that returns '{}' instead of example '/1/'.

I guess it's a problem with the serialization, no matter I created a JSonConverter<HierarchyId>. I read that OData uses it's own serializers so the converter won't work.

Also I create a IODataSerializerProvider but when the GetODataPayloadSerializer(Type type, HttpRequest request) is reached the type only gets EntityQueryable[Account] and not the property I would like to serialize.

I use HierarchyId in several classes, I won't like to add a handler for each class that use hierarchyid, is it some configuration I missing to serialize only the property I would like?

Answer 1

It appears that OData and System.Text.JsonJsonSerializer.Deserialize(jsonString) does not like the HierarchyId struct and is unable deserialize the values. When Net 8 and EF Core 8 are released in November, the issue be addressed, as EF Core 8 is fully supporting HierarchyId's.

In the meantime, I created a little workaround and dealt with the issue.

First, I am using Entity Framework, and have an abstract class that has the HierarchyId and two computed columns:

public HierarchyId HidNode { get; private set; } = null!;

private string _HidNodeString = "/";
DatabaseGenerated(DatabaseGeneratedOption.Computed)] 
public string HidNodeString
{
     get => _HidNodeString ;
     set
    {
        if (!string.IsNullOrWhiteSpace(value))
        {
            _HidNodeString = value;
            HidNode = HierarchyId.Parse(value);
        }
     }
}

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public short HidNodeLevel { get; set; }

Then in the Model Builder, create the computed columns:

builder.Property(p => p.HidNodeLevel).HasComputedColumnSql("HidNode.GetLevel()");
builder.Property(p => p.HidNodeNodeString).HasComputedColumnSql("HidNode.ToString()");
builder.HasIndex(e => new { e.HidNodeLevel, e.HidNode}).HasDatabaseName("IX_StorageLocation").IsUnique();

In SQL Server, the HierarchyId.ToString() method parses the HierarchyId and looks like this in the database:

HidNode	HidNodeString
0x	/
0x58	/1/
0x68	/2/
0x5AC0	/1/1/
0x5B40	/1/2/
0x6AC0	/2/1/
0x6B40	/2/2/

Then your entity can inherit the abstract class and seed the HierarchyId using the following:

entity.HidNodeString = "/";
entity.HidNodeString = "/1/";
entity.HidNodeString = "/1/1/";

Now OData will ignore the HierarchyId field, but populate the HidNodeString field, which populates the HierarchyId. You can reference the HierarchyId field without any issue in your code base.

Be certain to use a private setter for the HierarchyId and a short for the HidNodeLevel!

And you can also create an index with the HidNodeLevel and HidNodeString fields, so that you use those in your OData $orderBy query.

Answer 2

Depending on your goals it might be simpler to use binary representation of hierarchyid as string HidCode:

declare @OriginPath varchar(800) = '/1/2/'; 
declare @HidCode varchar(800) = convert(varchar(800), cast(hierarchyid::Parse(@OriginPath) as varbinary(800)), 1);
declare @Hid hierarchyid = cast(convert(varbinary(800), @HidCode, 1) as hierarchyid);
declare @HidPath varchar(800) = @Hid.ToString();
select OriginPath = @OriginPath, HidCode = @HidCode, Hid = @Hid, HidPath = @HidPath;

Result
----------------------------------------
OriginPath  HidCode  Hid        HidPath
/1/2/       0x5B40   0x5B40     /1/2/

You can use HidCode to sort your hierarchy correctly on client side.

For example, sorting by HidPath gives wrong result:

HidCode HidPath 
-----   --------
0x58    /1/
0xAA    /10/
0x68    /2/

The same query but ordered by HidCode makes the right sorting:

HidCode HidPath
-----   --------
0x58    /1/
0x68    /2/
0xAA    /10/

See more here: Combination of Id-ParentId and HierarchyId Approaches to Hierarchical Data