Entity Framework 6 and HierarchyId

This post is referring to the legacy Entity Framework EDMX file. I have read somewhere (I forget where) that the problem described below is not applicable to EF Core where the model is managed in a completely different way, either by code-first or scaffolding from an existing database.

If you create an Entity Framework 6 EDMX (data model) which contains tables with columns of type hierarchyid then you will get some warnings and the table will arrive in the designer with the offending columns removed. However, you can define and process those types of columns in a strongly-typed manner.

Add NuGet package Microsoft.SqlServer.Types to your project.

Create a partial class that contains the missing hierarchical columns, like this example:

using Microsoft.SqlServer.Types;
:
partial class SomeTable
{
  public SqlHierarchyId SomeNode { get; set; }
}

Unfortunately you can't use LINQ queries on the tables to process rows containing these types of columns. Write some helper methods that use traditional ADO.NET to execute a reader over the table and cast the row-column value to a .NET type like this:

var someNode = (SqlHierarchyId)reader.GetValue(1);
var otherNode = reader.IsDBNull(2) ? SqlHierarchyId.Null : (SqlHierarchyId)reader.GetValue(2)

Note how the second sample line deals with a nullable hierarchyid column.

Orthogonal Programming