Entity Framework 6 and HierarchyId

 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