.net Core中SqlHierarchyId的数据类型查询转换报错

项目类包配置

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

  <PropertyGroup>
	  <TargetFramework>net6.0</TargetFramework>
	  <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Dapper" Version="2.1.15" />
    <PackageReference Include="Microsoft.SqlServer.Types" Version="160.1000.6" />
    <PackageReference Include="Newtonsoft.Json" Version="13.0.3" />
    <PackageReference Include="System.Data.SqlClient" Version="4.8.5" />
  </ItemGroup>

</Project>

示例代码及表结构

CREATE TABLE Test01(
id INT PRIMARY KEY IDENTITY,
nPid sys.HIERARCHYID NULL
)


--示例数据
INSERT INTO dbo.Test01(nPid)
VALUES('/7/'),('/7/1/')

using Dapper;
using Microsoft.SqlServer.Types;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ClassLibrary1
{
    public class Simple2
    {
        public static void Main()
        {
            var sqlConnectionStr = "连接字符串";


            var sql = new SqlConnection(sqlConnectionStr);
            sql.Open();
            //使用object接收
            var c = sql.Query<object>("SELECT nPid FROM dbo.Test01").ToList();

            //使用实体接收
            var c1 = sql.Query<Test01>("SELECT nPid FROM dbo.Test01").ToList();
        }

        public class Test01
        {
            public SqlHierarchyId nPid { get; set; }
        }
    }
}

出现以下错误

Microsoft.SqlServer.Server.InvalidUdtException:“'Microsoft.SqlServer.Types.SqlHierarchyId' is an invalid user defined type, reason: no UDT attribute.”

原因

System.Data.SqlClient在core项目中不能识别到最新的core版本Microsoft.SqlServer.Types，需要对其降级到14.0.1016.290版本

解决方案

方案一、降级Microsoft.SqlServer.Types至14.0.1016.290版本

方案二、将System.Data.SqlClient替换至Microsoft.Data.SqlClient，因tsl协议不同，需要注意相应配置的改动