ITypeHandler is not used when creating parameters from list

[bmadzinski]

Hi,

I'm working with Dapper and Oracle database and using Guid data type on some of my columns. Due to that fact I had to create custom TypeHandler in order to properly convert .Net Guid to Oracle Guid string representation.

class CustomGuidTypeHandler : SqlMapper.TypeHandler<Guid>
{
    public override void SetValue(IDbDataParameter parameter, Guid value)
    {
        parameter.Value = value.FlipEndian().ToString("N").ToUpper();
    }

    public override Guid Parse(object value)
    {
        return new Guid((byte[])value).FlipEndian();
    }
}

FlipEndian implementation from: https://stackoverflow.com/questions/8064648/net-native-guid-conversion?lq=1

Everything works just fine for simple queries like these:

partial class Repository
{
    private readonly OracleConnection _oracleConnection;

    public Repository(OracleConnection oracleConnection)
    {
        _oracleConnection = oracleConnection;
    }

    public Task<Guid> GetFirstId()
    {
        return _oracleConnection.QueryFirstAsync<Guid>("SELECT ID FROM SOME_TABLE FETCH FIRST 1 ROWS ONLY");
    }

    public Task<bool> DoesExist(Guid id)
    {
        return _oracleConnection.ExecuteScalarAsync<bool>("SELECT COUNT(1) FROM SOME_TABLE WHERE ID = :id", new { id }
    }
}

Problem is when we try to use Guid inside collection, exception is gonna be thrown.

partial class Repository
{
    public Task<IEnumerable<Guid>> GetExisting(IEnumerable<Guid> ids)
    {
        return _oracleConnection.QueryAsync<Guid>("SELECT ID FROM SOME_TABLE WHERE ID IN :ids", new { ids });
    }
}

When I manually map Guid to string everything works just fine.

partial class Repository
{
    public Task<IEnumerable<Guid>> GetExistingMapGuidToStringManually(IEnumerable<Guid> ids)
    {
        return _oracleConnection.QueryAsync<Guid>(
            "SELECT ID FROM SOME_TABLE WHERE ID IN :ids",
            new
            {
                ids = ids.Select(x => x.FlipEndian().ToString("N").ToUpper()),
            }
        );
    }
}

I was debugging this and I think I have found the issue. When it comes to the single Guid parameters in the CreateParamInfoGenerator method the LookupDbType method is used to check if there is a custom handler for parameter type, and later on TypeHandlerCache is called to convert type.

internal static Action<IDbCommand, object?> CreateParamInfoGenerator(Identity identity, bool checkForDuplicates, bool removeUnused, IList<LiteralToken> literals)
{
    // [...]
    DbType? dbType = LookupDbType(prop.PropertyType, prop.Name, true, out ITypeHandler? handler);
    // [...]
    il.Emit(OpCodes.Call, typeof(TypeHandlerCache<>).MakeGenericType(prop.PropertyType).GetMethod(nameof(TypeHandlerCache<int>.SetValue))!); // stack is now [parameters] [[parameters]] [parameter]
    // [...]
}

But for the collection Guid parameter flow is different. The PackListParameters method is used, and there there is a call to LookupDbType in order to check if custom handler for parameter type exists, but later on handler is not used.

public static void PackListParameters(IDbCommand command, string namePrefix, object? value)
{
    // [...]
    dbType = LookupDbType(item.GetType(), "", true, out var handler);
    // [...]
    var tmp = listParam.Value = SanitizeParameterValue(item);
    // [...]

So this line: https://github.com/DapperLib/Dapper/blob/main/Dapper/SqlMapper.cs#L2187C29-L2187C86 throws an exception.

IMO this line:

var tmp = listParam.Value = SanitizeParameterValue(item);

needs to be replaced with something like this (use ITypeHandler)

var tmp = listParam.Value = handler is not null 
    ? handler.SetValue(listParam, item)
    : SanitizeParameterValue(item);