Serialize List<T> into google charts json datatable

Question

I'm looking for a generic way of serializing a List of objects into the google charts json data format.

This example is quite close, but it's using a datatable..

I expect this would involve some reflection and some maybe attrbutes on the model properties. Can anyone point me to a library or something?

Even better would be if I could serialize a query like this into the google charts format:

var results = from m in be.cmsMember
      where m.FirstLogin != null
      && m.FirstLogin >= BitCoinGoLive
      group m by

      new { Year = m.FirstLogin.Value.Year, Month = m.FirstLogin.Value.Month, Day =           m.FirstLogin.Value.Day } into grp
      select new
      {
                              Year = grp.Key.Year,
                              Month = grp.Key.Month,
                              Day = grp.Key.Day,
                              Count = grp.Count()
      };

Answer 1

I would create my own class hierarchy that matches Google's API and then use JSON.NET to serialize it. The possible data model:

public class Graph {
    public ColInfo[] cols { get; set; }
    public DataPointSet[] rows { get; set; }
    public Dictionary<string, string> p { get; set; }
}

public class ColInfo {
    public string id { get; set; }
    public string label { get; set; }
    public string type { get; set; }
}

public class DataPointSet {
    public DataPoint[] c { get; set; }
}

public class DataPoint {
    public string v { get; set; } // value
    public string f { get; set; } // format
}

Then an example usage:

var graph = new Graph {
    cols = new ColInfo[] {
        new ColInfo { id = "A", label = "set A", type = "string" },
        new ColInfo { id = "B", label = "set B", type = "string" },
        new ColInfo { id = "C", label = "set C", type = "string" }
    },
    rows = new DataPointSet[] {
        new DataPointSet {
            c = new DataPoint[] {
                new DataPoint { v = "a" },
                new DataPoint { v = "b", f = "One" }
            }
        }
    },
    p = new Dictionary<string, string>()
};

string json;
//var s = new JsonSerializer();
var s = new JavaScriptSerializer();
/*using (var sw = new StringWriter()) {
    s.Serialize(sw, graph);
    json = sw.ToString();
}*/
var sw = new StringBuilder();
s.Serialize(graph, sw);
json = sw.ToString();

You can use Linq's Select() to transform your data into Google's data model, then serialize it to JSON.

Answer 2

Here's a full working funciton which also works with anonymous types.

Carefull with dates though: these need to parsed on the client as well:e.g.

for (var i = 0; i < data.rows.length;i++ ) {
   data.rows[i].c[0].v = new Date(data.rows[i].c[0].v);
}



private string getGetJsonString<T>(IEnumerable<dynamic> list, dynamic row) {

    string header = "{\"cols\":[";
    PropertyInfo[] props = row.GetType().GetProperties();
    foreach (PropertyInfo p in props)
    {

        header += "{\"id\":\"" + p.Name + "\", \"label\":\"" + p.Name + "\",";
        switch (p.PropertyType.Name)
        {
            case "Int32":
                header += "\"type\":\"number\"";
                break;
            case "DateTime":
                header += "\"type\":\"date\"";
                break;
            default:
                header += "\"type\":\"string\"";
                break;
        }
        header += "},";
    }
    header = header.Substring(0, header.Length - 1);
    header += "]";

    StringBuilder json = new StringBuilder();
    json.Append(header + ",\"rows\":[");

    bool first = true;
    foreach (dynamic a in list)
    {
        string jRow = "{\"c\":[";
        if (first)
            first = false;                    
        else
            jRow = "," + jRow;

        foreach (PropertyInfo p in props)
        {

            // todo get other fieldtypes from http://code.google.com/apis/chart/interactive/docs/reference.html#dataparam
            switch (p.PropertyType.Name)
            {
                case "Int32":
                    jRow += "{\"v\":";
                    jRow += p.GetValue(a,null).ToString();
                    jRow += "},";
                    break;
                case "DateTime":
                    jRow += "{\"v\":\"";
                    DateTime d = ((DateTime)p.GetValue(a, null));
                    //jRow += d.DayOfYear;
                    //jRow += "\\/Date("+d.Ticks+")\\/";
                    jRow += d.ToString("yyyy-MM-dd");
                    //jRow += "new Date(" + d.Ticks+ ")";
                    jRow += "\"},";
                    break; 
                default:
                    jRow += "{\"v\":\"";
                    jRow += p.GetValue(a,null).ToString();
                    jRow += "\"},";
                    break;
            }

        }
        jRow = jRow.Substring(0, jRow.Length - 1);
        json.Append(jRow + "]}");
    }

    json.Append("]}");


    return json.ToString() ;
}