Could not load file or assembly Microsoft.SqlServer.Types

[mgroves]

Could not load file or assembly Microsoft.SqlServer.Types

[dotMorten]

do you see any major issues with this approach?

You mean apart from it being way harder than I wish it should be? No :-)

[dotMorten]

I think this is a duplicate of #51, due to #29

[mgroves]

Thanks for reading my issue.

I suspected that #29/#51 is related to my issue, when I was reading through them, but if this is related to Microsoft.Data.SqlClient not being supported, then my assumption is that using System.Data.SqlClient will work. Is that correct?

But I get the same exception when using either one with dotMorten.Microsoft.SqlServer.Types: "Could not load file or assembly Microsoft.SqlServer.Types..."

For what I'm working on, I don't really care which one I have to use, but neither seems to work.

[dotMorten]

@mgroves You might be hitting the assembly redirect limitation. Did you see the very last line of code in the readme?
https://github.com/dotMorten/Microsoft.SqlServer.Types/blob/main/README.md

Also see this issue:
#22

[dotMorten]

Oh hold on... you need to remove <PackageReference Include="Microsoft.SqlServer.Types" Version="14.0.1016.290" />. That's for .NET Framework only, and what this library tries to replace.

[mgroves]

You'll notice my first example does NOT have Microsoft.SqlServer.Types, I instead have dotMorten.Microsoft.SqlServer.Types. However, when I switch back to Microsoft.SqlServer.Types, the program works (albeit with the .NETFramework warning).

I didn't see the comment about assembly redirect. But I'm not even reading individual columns. So perhaps that IS the issue I'm hitting. Should I put an assembly redirect in, so any attempts to use Microsoft.SqlServer.Types are forwarded to dotMorten.Microsoft.SqlServer.Types? Can/should I do that in PropertyGroup in the csproj?

[dotMorten]

right but in your first example you're using the sql client that isn't supported.

[mgroves]

I understand. Here's my new csproj; still getting the same exception. I think I've tried all the permutations now :)

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

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net5.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Dapper" Version="2.0.90" />
    <PackageReference Include="dotMorten.Microsoft.SqlServer.Types" Version="1.3.0" />
    <PackageReference Include="System.Data.SqlClient" Version="4.8.2" />
  </ItemGroup>

</Project>

Program.cs

using System;
using Dapper;
using System.Data.SqlClient;

namespace SqlTest
{
    class Program
    {
        static void Main(string[] args)
        {
            var conn = new SqlConnection("Server=localhost;Database=AdventureWorks;Trusted_Connection=True;");

            var addresses = conn.Query("SELECT TOP 10 * FROM Person.Address");

            foreach (var address in addresses)
            {
                Console.WriteLine(address);
            }

            conn.Dispose();
        }
    }
}

Exception:

Unhandled 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.
File name: 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'
at System.Reflection.RuntimeAssembly.InternalLoad(ObjectHandleOnStack assemblyName, ObjectHandleOnStack requestingAssembly, StackCrawlMarkHandle stackMark, Boolean throwOnFileNotFound, ObjectHandleOnStack assemblyLoadContext, ObjectHandleOnStack retAssembly)
at System.Reflection.RuntimeAssembly.InternalLoad(AssemblyName assemblyName, RuntimeAssembly requestingAssembly, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, AssemblyLoadContext assemblyLoadContext)
at System.Reflection.Assembly.Load(AssemblyName assemblyRef)
at System.Data.SqlClient.SqlConnection.ResolveTypeAssembly(AssemblyName asmRef, Boolean throwOnError)
at System.Data.SqlClient.SqlConnection.<>c__DisplayClass133_0.b__0(AssemblyName asmRef)
at System.TypeNameParser.ResolveAssembly(String asmName, Func2 assemblyResolver, Boolean throwOnError, StackCrawlMark& stackMark) at System.TypeNameParser.ConstructType(Func2 assemblyResolver, Func4 typeResolver, Boolean throwOnError, Boolean ignoreCase, StackCrawlMark& stackMark) at System.TypeNameParser.GetType(String typeName, Func2 assemblyResolver, Func4 typeResolver, Boolean throwOnError, Boolean ignoreCase, StackCrawlMark& stackMark) at System.Type.GetType(String typeName, Func2 assemblyResolver, Func4 typeResolver, Boolean throwOnError) at System.Data.SqlClient.SqlConnection.CheckGetExtendedUDTInfo(SqlMetaDataPriv metaData, Boolean fThrow) at System.Data.SqlClient.SqlDataReader.GetValueFromSqlBufferInternal(SqlBuffer data, _SqlMetaData metaData) at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i) at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i) at Dapper.SqlMapper.<>c__DisplayClass171_0.<GetDapperRowDeserializer>b__0(IDataReader r) in /_/Dapper/SqlMapper.cs:line 1877 at Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+MoveNext() in /_/Dapper/SqlMapper.cs:line 1102 at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)
at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable1 commandTimeout, Nullable1 commandType) in //Dapper/SqlMapper.cs:line 725
at Dapper.SqlMapper.Query(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable1 commandTimeout, Nullable1 commandType) in //Dapper/SqlMapper.cs:line 652
at SqlTest.Program.Main(String[] args) in c:\zproj\SqlTest\SqlTest\Program.cs:line 13

[dotMorten]

Correct. This is the assembly redirect issue you're hitting. You can't use GetValue on UDT types, unless you're on .NET Framework - just no way around that due to the way Microsoft made their UDT support.

[dotMorten]

I don't really know Dapper, but a quick google on UDT and Dapper seems to indicate you need to make a type handler that takes care of the conversion using the supported way to deserialize it.

[mgroves]

So if Dapper is using GetValue behind the scenes, that's what leading to the issue? I will take a look at a quick google and see what I come up with. Thank you for the help.

[dotMorten]

GetValue will try and convert to a type that is defined in an assembly that is only available with .NET Framework. This library is trying to address this, but unfortunately the automatic type lookup won't work (since there's not a good way for this assembly to fake the identity of the original assembly).
There are several issues in the dapper repo talking about dealing with sqlgeography/sqlgeometry

[mgroves]

Okay, so after going down the rabbit hole a bit, I believe I have a workaround!

First, I went here: DapperLib/Dapper#1466 which mentions this very project :)
Second, I found #22 - #22 where @MaceWindu mentions a custom assembly resolver
Finally, I went to @MaceWindu's StackOverflow answer here - https://stackoverflow.com/questions/57012534/cant-cast-sqlgeography-when-withdrawing-data-from-db/57373622#57373622

I implemented that (almost) exactly in the console app. Success! No more exception, and no more .NETFramework warnings either.

@dotMorten, do you see any major issues with this approach?