Netcore 3.1 UTF8JSON Serializer adding literal Key and Value to result

Question

Netcore 3.1 UTF8JSON Serializer adding literal Key and Value to dapper query result.

public async Task<List<dynamic>> JsonQs(string sql, object param = null, CommandType commandType = CommandType.StoredProcedure)
{
    using SqlConnection conn = new SqlConnection(_config.GetConnectionString("DefaultConnection"));
    IEnumerable<dynamic> data = await conn.QueryAsync(sql, param, commandType: commandType);
    return data.ToList();
}

public async Task<IActionResult> GetRecord([FromQuery] ReportFilter model)
{
    var res = await _repo.JsonQs("GetBreakdown", model);
    return Ok(res);
}

Output:

[[{"Key":"Id","Value":"INV1"},{"Key":"Id","Value":"INV2"} ]]

Expected output:

[{"Id":"INV 1"},{"Id":"INV2"}]

Answer 1

data.ToList() returns a list of KeyValuePair<TKey,TValue> list, either because await conn.QueryAsync(sql, param, commandType: commandType); returns a Dictionary<TKey,TValue> or KeyValuePair<TKey,TValue> IEnumerable Interface.

You need to map the result from KeyValuePair<TKey,TValue> using: Select(i => new { Id = i.Value })

public async Task<List<dynamic>> JsonQs(string sql, object param = null, CommandType commandType = CommandType.StoredProcedure)
{
    using SqlConnection conn = new SqlConnection(_config.GetConnectionString("DefaultConnection"));
    IEnumerable<dynamic> data = await conn.QueryAsync(sql, param, commandType: commandType);
    return data.Select(i => new { Id = i.Value }).ToList();
}
    
public async Task<IActionResult> GetRecord([FromQuery] ReportFilter model)
{
    var res = await _repo.JsonQs("GetBreakdown", model);
    return Ok(res);
}

Answer 2

Finally solved. The solution is UTF8JSON doesn't play nicely with dynamic. Changed the type to a class/POCO and it's all solved.

public async Task<List<User>> JsonQs(string sql, object param = null, CommandType commandType = CommandType.StoredProcedure)
{
    using SqlConnection conn = new SqlConnection(_config.GetConnectionString("DefaultConnection"));
    IEnumerable<User> data = await conn.QueryAsync(sql, param, commandType: commandType);
    return data;
}