"No mapping exists" error when using a TypeHandler and a collection parameter

[tlecomte]

Dapper is throwing an error when running a query in the following scenario:

• the parameter is a collection of objects of a given type T
• and the type T is not natively supported by the SQL client
• and a TypeHandler is defined for this type T

Example:

// a type that is not natively supported by SqlClient
public struct LocalDate
{
    public int Year { get; set; }
    public int Month { get; set; }
    public int Day { get; set; }
}

// a type handler
public class LocalDateHandler : SqlMapper.TypeHandler<LocalDate>
{
    private LocalDateHandler() { /* private constructor */ }

    // Make the field type ITypeHandler to ensure it cannot be used with SqlMapper.AddTypeHandler<T>(TypeHandler<T>)
    // by mistake.
    public static readonly SqlMapper.ITypeHandler Default = new LocalDateHandler();

    public override LocalDate Parse(object? value)
    {
        var date = (DateTime)value!;
        return new LocalDate { Year = date.Year, Month = date.Month, Day = date.Day };
    }

    public override void SetValue(IDbDataParameter parameter, LocalDate value)
    {
        parameter.DbType = DbType.DateTime;
        parameter.Value = new DateTime(value.Year, value.Month, value.Day);
    }
}

// the failing method
public void RunQueryWithParamCollectionAndCustomTypeHandler()
{
    SqlMapper.AddTypeHandler(typeof(LocalDate), LocalDateHandler.Default);

    var singleParameter = new { SingleDate = new LocalDate { Year = 2014, Month = 7, Day = 25 } };

    // this succeeds
    var resultSingle = connection.Query<int>("SELECT 1 WHERE '2014-07-25' = @SingleDate", singleParameter).Single();

    var parameters = new
    {
        ListOfDates = new List<LocalDate>
        {
            new() { Year = 2014, Month = 7, Day = 25 },
            new() { Year = 2014, Month = 7, Day = 26 },
        }
    };

    // this fails with 'No mapping exists from object type LocalDate to a known managed provider native type.'
    var result = connection.Query<int>("SELECT 1 WHERE '2014-07-25' IN @ListOfDates", parameters).Single();
}