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"}]

Probably stop using `dynamic` and dealing with regular types would help. There's no reason for `dynamic`. — aepot, Apr 02 '21 at 22:42

score 2 · Answer 1 · edited Apr 06 '21 at 11:01

2

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);
}

edited Apr 06 '21 at 11:01

Palle Due

5,929
4
17
32

answered Apr 02 '21 at 16:52

McKabue

2,076
1
19
34

even without return data.ToList(); If i do return data; I still get same result – froodo Apr 02 '21 at 17:00
I have edited the answer to include full code. you must include: `return data.Select(i => new { Id = i.Value }).ToList();` – McKabue Apr 02 '21 at 17:03
result is [{},{},{},{},{}]....empty objects with correct counts. – froodo Apr 02 '21 at 17:04

score 0 · Answer 2 · answered Apr 03 '21 at 14:03

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;
}

Netcore 3.1 UTF8JSON Serializer adding literal Key and Value to result

2 Answers2