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?

score 0 · Answer 1 · answered Aug 08 '23 at 00:47

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.

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

1 Answers1