Using SqlHierarchId throws System.InvalidCastException with Dapper

Question

Using SqlHierarchId throws System.InvalidCastException with Dapper

.net dapper hierarchyid

Question

I want to use SqlHierarchId in my .net project but when I try to read a SqlHierarchId from DB, System.InvalidCastException will occur. I have written a simple code which it's accessible in bellow link, to show this problem:

SqlHierarchyIdTest

var connection = new SqlConnection("Data Source=.;Initial Catalog=tempdb;Integrated Security=True");
var val = connection.Query<Microsoft.SqlServer.Types.SqlHierarchyId>("select @Path", new {Path = Microsoft.SqlServer.Types.SqlHierarchyId.Parse("/1/2/3/")}).Single();

I trace code and reach the QueryImpl<T> in SqlMapper class. When Dapper tries to compare the type of value which is read by SqlDataReader, to the type of generic, confront two types of SqlHierarchyId!

Fred

View more on Stack Overflow

SeanOB · Accepted Answer · 2/9/2018 5:57:41 AM

It looks as though one reference is bundled with the Dapper libraries, while the higher version is the one you installed or referenced from your machine.

Try add this to your app.config file to force all assemblies called Microsoft.SqlServer.Types to use the newest version:

<runtime>
  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
    <dependentAssembly>
       <assemblyIdentity name="Microsoft.SqlServer.Types" publicKeyToken="89845dcd8080cc91" culture="neutral" />
       <bindingRedirect oldVersion="10.0.0.0" newVersion="14.0.0.0" />
    </dependentAssembly>
  </assemblyBinding>
</runtime>

Microsoft Docs on assembly redirects: Microsoft Docs - Redirecting Assembly Versions

Using SqlHierarchId throws System.InvalidCastException with Dapper

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator