Npgsql: JSON.NET plugin doesn't work with Dapper

[vicpon]

I'm running into an issue with the JSON.NET plugin and I think it's related to this #2439 .

I'm using Dapper and Npgsql to write a generic query method:

using (var connection = new NpgsqlConnection(this._connectionString))
{
    connection.Open();
    string query = $"select {fields} from {this._table} c";
    DynamicParameters sqlParams = null;
    if (predicate != null)
    {
        query += " where " + predicate;
        sqlParams = new DynamicParameters();
        foreach (var param in parameters)
        {
            sqlParams.Add(param.Item1, param.Item2);
        }
    }
    if (take.HasValue && take.Value > 0)
    {
        query += $" limit {take.Value}";
    }
    if (skip.HasValue && skip.Value > 0)
    {
        query += $" offset {skip.Value}";
    }
    return (await connection.QueryAsync<TAnything>(query, sqlParams)).AsList();
}

If I use a TypeHandler (as described here https://radekmaziarka.pl/2018/01/22/dapper-json-type-custom-mapper/), it works fine. But if I use the plugin, it can't convert the string to the CLR type. I am registering my type like this:

NpgsqlConnection.GlobalTypeMapper.UseJsonNet(new[] { typeof(MyClrType) });

Exception thrown:

at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in C:\projects\dapper\Dapper\SqlMapper.cs:line 3609
at Deserializea4a4ae80-8917-46c0-a729-28b024dfdd17(IDataReader )
at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command)

Inner Exception thrown:

   at System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider)
   at System.String.System.IConvertible.ToType(Type type, IFormatProvider provider)
   at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
   at System.Convert.ChangeType(Object value, Type conversionType)
   at Deserializea4a4ae80-8917-46c0-a729-28b024dfdd17(IDataReader )

I assume this is because Dapper isn't calling reader.GetFieldValue<MyClrType>(0) and that the aforementioned issue is attempting to address this, correct? If this is the case, can't you implement something like the blog post linked?

[YohDeadfall]

Dapper uses the Item property of IDataRecord, so Npgsql returns a string a result because there is no requested type and the default type for the JSON handler is string. Therefore, Dapper tries to convert the value using Convert.ChangeType which internally calls string.ToType, but string knows nothing about your type.

To conclude, you have to use the Dapper's type handler even if you registered the plugin.

[vicpon]

@YohDeadfall, thanks for the reply. I know that Dapper doesn't deserialize the same way that the plugin does so they're not compatible. I posted this issue to hopefully contribute, at least an idea, to a fix for #2439 because it would be nice if the plugin worked with Dapper.

[roji]

@vicpon an important thing to understand, is that if Dapper itself gets the value via an untyped getter (e.g. IDataReader.Item), it's not conveying the information of which type the user is requesting. So Npgsql doesn't know which type to deserialize to - there could be any number of user POCO types being mapped to JSON at any given point.

So I don't really see what we could do at our level to make things better...