Cannot read SqlGeography with Dapper

Question

I followed alot of SO questions and I am getting simillar error but I cannot fix this issue.

My Dapper version is 1.50.1 My Microsoft.SqlServer.Types version is 11.0.2

I have added binding redirect in configuration file as follows:

<runtime>
    <dependentAssembly>
      <assemblyIdentity name="Microsoft.SqlServer.Types" publicKeyToken="89845dcd8080cc91" culture="neutral" />
      <bindingRedirect oldVersion="10.0.0.0" newVersion="11.0.0.0" />
    </dependentAssembly>
</runtime>

The error I get is:

Error parsing column 2 (Location=POINT (-122.349 47.651) - Object)

While the inner exception is:

[A]Microsoft.SqlServer.Types.SqlGeography cannot be cast to [B]Microsoft.SqlServer.Types.SqlGeography. Type A originates from 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' in the context 'Default' at location 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.Types\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Types.dll'. Type B originates from 'Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' in the context 'Default' at location 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.Types\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Types.dll'.

If I just do a read to dynamic object everything is all right but if I do read to my own type that has SqlGeography I get this error.

I thought I did all as recommended but it seems I missed something.

Answer 1

Try reading this article on topic SQL CLR Data Types (geometry, geography, and hierarchyid).

Alternatively, try adding this in your connectionString:

"Type System Version=SQL Server 2012", to force SqlClient to load version 11.0 of the assembly

Answer 2

@Marc Gravell did a good job about that see reference below and it will save your life.

SqlGeography

Issue

For ASP.NET applications, add the following line of code to the Application_Start method in Global.asax.cs:

SqlServerTypes.Utilities.LoadNativeAssemblies(Server.MapPath("~/bin"));

For desktop applications, add the following line of code to run before any spatial operations are performed:

SqlServerTypes.Utilities.LoadNativeAssemblies(AppDomain.CurrentDomain.BaseDirectory);

The .LoadNativeAssemblies method will get the correct assembly for you base on CPU architecture.

In bin folder you should have this.