Sqlclient: Unable to cast object of type 'Microsoft.SqlServer.Types.SqlHierarchyId' to type 'Microsoft.Data.SqlClient.Server.IBinarySerialize'.

[alvipeo]

Please see this - https://stackoverflow.com/q/63430419/2896495.

[cheenamalhotra]

Pasting description from StackOverflow:

---

I work with SQL Server Db in my .Net Core 3.1 project and some stored procedures and views have hierarchyid types for parameters and data.

I use Microsoft.Data.SqlClient package. And when I try to read data with SqlDataReader I get the exception:

System.IO.FileNotFoundException : Could not load file or assembly 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. The system cannot find the file specified.

ok, I tried to use Microsoft.SqlServer.Types as it suggests but this package is not .NET Standard and it doesn't work.

Also, I found EntityFrameworkCore.SqlServer.HierarchyId but when I use it I get:

System.InvalidCastException : Unable to cast object of type 'Microsoft.SqlServer.Types.SqlHierarchyId' to type 'Microsoft.Data.SqlClient.Server.IBinarySerialize'.

So how on Earth can one use HierarchyId type in .NET Core 3.1?

I'm planning to host this solution on linux.

UPDATE
I do use Microsoft.Data.SqlClient 2.0 which is compatible with .NET Core. Also, I added then EntityFrameworkCore.SqlServer.HierarchyId, and I get this error:

System.InvalidCastException : Unable to cast object of type 'Microsoft.SqlServer.Types.SqlHierarchyId' to type 'Microsoft.Data.SqlClient.Server.IBinarySerialize'.

Here's the .csproj:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>netstandard2.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="EntityFrameworkCore.SqlServer.HierarchyId" Version="1.1.0" />
    <PackageReference Include="Microsoft.Data.SqlClient" Version="2.0.0" />
    <PackageReference Include="Newtonsoft.Json" Version="12.0.3" />
  </ItemGroup>

  <ItemGroup>
    <Folder Include="Helpers\" />
  </ItemGroup>

</Project>

No luck so far.

UPDATE 2
This is the code where the exception is thrown:

using (SqlDataReader reader = await detailsCmd.ExecuteReaderAsync())
{
  while (reader.Read())
  {
    details.Add(new HierarchyDetails
    {
      Id = reader.GetInt32(0),
      groupPath = reader.GetValue(1).ToString(), // <==== EXCEPTION
      name = reader.GetString(2),
      optionalData = reader.IsDBNull(3) ? null : reader.GetString(3)
    });
  }
}

And the table has the only row:

id  groupPath   culture name    optionalData
24  0x58        en-US   testing 

[cheenamalhotra]

@ajcvickers @bricelam

Since the customer is using EFCore library "EntityFrameworkCore.SqlServer.HierarchyId" here, do you have an example to demonstrate how it shall work for customer's case?

[bricelam]

EntityFrameworkCore.SqlServer.HierarchyId is not supported by Microsoft. It uses dotMorten.Microsoft.SqlServer.Types on .NET Core which is also not supported by Microsoft.

Until Microsoft.SqlServer.Types is available on .NET Core, the only supported way to use hierarchyid values is to convert them to another type in your queries (e.g. using ToString() and Parse()) or by working directly with the UDT bytes on the client:

// Read
var bytes = dataReader.GetSqlBytes(columnOrdinal).Value;

// Write
var parameter = command.Parameters.AddWithValue(parameterName, new SqlBytes(bytes));
parameter.SqlDbType = SqlDbType.Udt;
parameter.UdtTypeName = "hierarchyid";

The binary format is documented in MS-SSCLRT.

[cheenamalhotra]

Closing issue as solution provided above.