Dapper result to json (using fastjson)

Question

===== UPDATED 8/20/2016 =====

latest version of fastjson can now handle Dictionary<string, ?> type correctly, my problem is solved now.

=============================

I'm using fastjson to serialize the query result from Dapper, the table in DB has data like this:

id | name | price
1  | x    | 100
2  | y    | 200
....

And when I

using Dapper;
using fastJSON;
// ....
JSON.Parameters.KVStyleStringDictionary = false;
// ....
result = JSON.toJSON(conn.Query("SELECT * FROM tableX"));

I want the result to be:

[{"id":1,"name":"x","price":100},{"id":2,"name":"y","price":200},...]

However the actual result outputs:

[[{"Key":"id","Value":1},{"Key":"name","Value":"x"},{"Key":"price","Value":100}],
[{"Key":"id","Value":2},{"Key":"name","Value":"y"},{"Key":"price","Value":200}]...]

Lots of key-value pairs are generated which looks redundant.

Is there a way to get the correct result ?

Or should I switch to another JSON serializer ?

========== UPDATED ==========

makubex88's answer indicates that I can create a customized class mapping the table and use conn.Query<myClass> to get the correct json, though it works for this scenario, it looks like I have to create hundreds of classes for every table in DB to get ideal json result, which is indeed tiring work for me. (Thanks any way:P)

Any alternative solutions would be highly appreciated!

ineztia · Accepted Answer · 2016-05-16T03:35:38.233

I found a solution to handle it (however it may lose some efficiency), to achieve this, I write my own QueryEx method, each row in query result is an IDictionary object:

public IEnumerable<IDictionary> QueryEx(IDbConnection conn, string sql, object argSet = null) {
    var result = conn.Query(sql, argSet) as IEnumerable<IDictionary<string, object>>;
    return result.Select(r => r.Distinct().ToDictionary(d => d.Key, d => d.Value));
}

And

result = JSON.toJSON(conn.QueryEx("SELECT * FROM tableX"));
// output: [{"id":1,"name":"x","price":100},{"id":2,"name":"y","price":200},...]

reason: fastJSON can only parse IDictionary interface correctly, any generic versions of IDictionary will be parsed as Key-Value pair lists

score 1 · Answer 2 · edited May 15 '16 at 17:03

1

Try to create a class for your output in JSON then you can serialized it in JSON.

//your class
    public class Item
    {
        int ID;
        public string Name;
        public double Price;
    }
//code:
    List<Item> = conn.Query<Item>("SELECT * FROM tableX").AsList();
    var result = Json(Item, JsonRequestBehavior.AllowGet);

edited May 15 '16 at 17:03

Marc Gravell

1,026,079
266
2,566
2,900

answered May 15 '16 at 16:31

Jeric Cruz

1,899
1
14
29

3

I added the missing ``; I also changed `ToList()` to dapper's `AsList()` - avoids an alloc/copy in this scenario – Marc Gravell May 15 '16 at 17:04
1

Though it works, it looks like I have to create classes for every table in DB (hundreds of tables) to get ideal json result, which is indeed tiring work for me. @makubex88 – ineztia May 15 '16 at 23:34
@MarcGravell Is this general guidance on .AsList()? I.e. should I replace all usages of .ToList() to .AsList() when returning lists using Dapper? – StuartQ Oct 25 '17 at 09:07
1

@StuartQ I would say that it depends on your scenario, if you want code to force the compiler to reify the result or to create a new list then use `.ToList()`. You can refer to this question (https://stackoverflow.com/questions/2774099/tolist-does-it-create-a-new-list). `.AsList()` is just simply casting your current list(if that is a list) without allocating / copying. – Jeric Cruz Oct 25 '17 at 09:46
2

@StuartQ yes, I would say so – Marc Gravell Oct 25 '17 at 09:59

Dapper result to json (using fastjson)

2 Answers2