How do I implement HierarchyId in EF Core 6 in C#

Question

I am developing a simple project management system and needed to manage the WBS (basicly a hierarchy of tasks). I spent several days combing through blog after blog after blog to figure out how to setup the HierarchyId using EF Core and SQL Server. I ran into a lot of errors about the type not being supported by the Db and converting to a CLR type (i think it was). Which drove me to try and do mapping in my database context file, none of which worked.

So I wanted to share a summary of what ended up working with the hope it will help someone else.

Answer 1

When you read blogs on implementing HierarchyId, you have to be very conscious of EF 6 vs EF Core 6. Some blogs articles will not be specify and the two and the solutions are not the same. I think some of my problem was MY inexperience and so I had pieces of a solution from EF and some from EF Core.

I am working on .Net 6, EF Core 6, and Sql Server (latest version).

You should already have

     Microsoft.EntityFrameworkCore
     Microsoft.EntityFrameworkCore.Tools
     Microsoft.EntityFrameworkCore.SqlServer

The only additional package you need is EntityFramworkCore.SqlServer.HierarchyId. The current version is 3.0.1.

In Program.cs add UseHierarchyId() to your UseSqlServer like this:

    builder.Services.AddDbContextFactory<DatabaseContext>(options =>
        options.UseSqlServer(connectionString, x => x.UseHierarchyId()));

When you create your database model add using Microsoft.EntityFrameworkCore; Here is an example of my model, you will see the HierarchyId at the bottom.

    public class TaskModel
    {
        [Key]
        public int Id { get; set; }
        [Required]
        public int ProjectId { get; set; }
        [Required]
        public string TaskName { get; set; }
        public string TaskDescription { get; set; }
        public DateTime? EstimatedStartDate { get; set; }
        public DateTime? EstimatedEndDate { get; set; }
        public DateTime? ActualStartDate { get; set; }
        public DateTime? ActualEndDate { get; set; }
        public ICollection<TaskStatusModel> TaskStatus { get; set; }
        public ICollection<TaskTimeModel> TaskTime { get; set; }
        public ICollection<TaskResourceModel> TaskResource { get; set; }
        public HierarchyId Level { get; set; }
    }

When you run Add-Migration Update-Database it will create the table with the HierarchyId type as expected.

One odd behavior (or bug) I've noticed is the HierarchyId will have an error before or during a build. You'll also see that the EntityFramworkCore.SqlServer.HierarchyId package is missing from the list in VS. From reading, it may be a compatibility problem. I don't know.

I find that you just rebuild again and it will resolve itself. This tends to happen alternately with successful builds. I've not found a solution to this yet.