Can dapper deserialize json stored as text?

Question

public class MyType
{
    public int Id { get; set;}
    public int[] MyArray { get; set; }
}

var sql = "SELECT id, MyArrayAsJson as MyArray";
var x = await connection.QueryAsync<MyType>(sql);

I have a string stored in the database which looks like json: [1,2,3,4,5]

When I query the db with Dapper, I would like dapper to deserialize to an object, MyType. Dapper wants MyArrayAsJson to be a string because it is, but I want it to deserialize to an int array. Is this possible?

Answer 1

public class JsonTypeHandler : SqlMapper.ITypeHandler
{
    public void SetValue(IDbDataParameter parameter, object value)
    {
        parameter.Value = JsonConvert.SerializeObject(value);
    }

    public object Parse(Type destinationType, object value)
    {
        return JsonConvert.DeserializeObject(value as string, destinationType);
    }
}


SqlMapper.AddTypeHandler(typeof(int[]), new JsonTypeHandler());

Answer 2

Dapper cannot do that, but you can do that!

It is just a little work using Dapper Multi Mapping

Select your [JsonColumn] as a string, and while mapping the results deserialize the JSON column, for example, if you have a Foo class and you want to deserialize the [JsonColumn] to it, do that:

using (var db = _context.Connect())
{
    return await db.QueryAsync<Foo, string, Foo>(query, (foo, jsonColumnString) =>
    {
        return JsonSerializer.Deserialize<Foo>(jsonColumnString);
    },
    splitOn: "JsonColumn",
    param: parameters);
}