Unable to cast object of type 'ClickHouse.Client.Numerics.ClickHouseDecimal' to type 'System.Nullable`1[System.Decimal]

[joshbartley]

Version: 6.7.2

When using Dapper's connection.QueryAsync<ModelClass>(commandDefintion) with a property of decimal?, the below error gets thrown on mapping. Clickhouse table schema includes a Nullable(Decimal(18, 2)) property that has it's value set to 123.00

var commandDefintion = new CommandDefinition(sqlString, dapperParameters, flags: CommandFlags.NoCache, commandTimeout: 60 * 4);
var results = await connection.QueryAsync<UniverseSearchReturnRow>(commandDefintion);

System.Data.DataException: Error parsing column 9 (DecimalColumn=123.00 - Object)
       ---> System.InvalidCastException: Unable to cast object of type 'ClickHouse.Client.Numerics.ClickHouseDecimal' to type 'System.Nullable`1[System.Decimal]'.
         at Deserializeee96f26b-a164-4613-be13-f787cf48453c(IDataReader )
         --- End of inner exception stack trace ---
         at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in /_/Dapper/SqlMapper.cs:line 3706
         at Deserializeee96f26b-a164-4613-be13-f787cf48453c(IDataReader )
         at Dapper.SqlMapper.ExecuteReaderSync[T](IDataReader reader, Func`2 func, Object parameters)+MoveNext() in /_/Dapper/SqlMapper.Async.cs:line 976
         at System.Linq.Enumerable.SelectEnumerableIterator`2.ToList()

[DarkWanderer]

Hi,

Yes, it seems to be a compatibility issue between ClickHouse.Client's type system and the way Dapper performs conversions

As an interim solution, does using a handler like:

    private class ClickHouseNullableDecimalHandler : SqlMapper.TypeHandler<decimal?>
    {
        public override void SetValue(IDbDataParameter parameter, decimal? value) => parameter.Value = value.ToString(CultureInfo.InvariantCulture);

        public override decimal? Parse(object value) => value switch
        {
            DBNull => null,
            null => null,
            ClickHouseDecimal chd => chd.ToDecimal(CultureInfo.InvariantCulture),
            IConvertible ic => Convert.ToDecimal(ic),
            _ => throw new ArgumentException(null, nameof(value))
        };
    }


    SqlMapper.AddTypeHandler(new ClickHouseNullableDecimalHandler ());

improve the situation for you?

[joshbartley]

I think that could work. For now we went to manual mapping because Dapper doesn't have a per query type handler system and we have way to many places do double check for that instead of writing out like 15 lines of mapping code. This also allowed us access to the QueryStats.

[DarkWanderer]

What I'm suggesting is to add this handler to your code

    private class ClickHouseNullableDecimalHandler : SqlMapper.TypeHandler<decimal?>
    {
        public override void SetValue(IDbDataParameter parameter, decimal? value) => parameter.Value = value.ToString(CultureInfo.InvariantCulture);

        public override decimal? Parse(object value) => value switch
        {
            DBNull => null,
            null => null,
            ClickHouseDecimal chd => chd.ToDecimal(CultureInfo.InvariantCulture),
            IConvertible ic => Convert.ToDecimal(ic),
            _ => throw new ArgumentException(null, nameof(value))
        };
    }

SqlMapper.AddTypeHandler(new ClickHouseNullableDecimalHandler ());

This is a static operation and should not require using manual mapping. As another workaround, you can try using UseCustomDecimals=false in connection string to revert to .NET decimals

[joshbartley]

I understand that, this is an existing code base connected to more than Clickhouse with Dapper and taking over the default mapping for all Dapper queries is more of a risk (and testing effort) then I'm willing to take versus mapping out a few properties. I'll check out the connection string change too. I've attempted a fork but 4.6.2 isn't installed on my laptop and I can't get a failing unit test yet for the decimal? conversion.