ITypeHandler is not used when creating parameters from list

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);

Start in Dapper/SqlMapper.cs at PackListParameters and compare its type-handler lookup with CreateParamInfoGenerator. Verify how the handler is expected to set each list parameter, then confirm that a registered Guid handler is applied for collection values and that the Oracle-style query no longer throws.