根据特定规则将数据表转换为JSON(Converting datatable into JSON based on certain rules)

类：

public class DataCls { public int Year { get; set; } public string Month { get; set; } public int MonthOrder { get; set; } public string category { get; set; } public int Val { get; set; } }

数据：

List<DataCls> dlist = new List<DataCls>(); DataCls dat = new DataCls(); dat.Year = 2015; dat.Month = "Jan"; dat.MonthOrder = 1; dat.category = "A"; dat.Val = 1; dlist.Add(dat); dat = new DataCls(); dat.Year = 2015; dat.Month = "Jan"; dat.MonthOrder = 1; dat.category = "B"; dat.Val = 2; dlist.Add(dat); dat = new DataCls(); dat.Year = 2015; dat.Month = "Jan"; dat.MonthOrder = 1; dat.category = "C"; dat.Val = 3; dlist.Add(dat); dat = new DataCls(); dat.Year = 2015; dat.Month = "Feb"; dat.MonthOrder = 2; dat.category = "A"; dat.Val = 5; dlist.Add(dat); dat = new DataCls(); dat.Year = 2015; dat.Month = "Feb"; dat.MonthOrder = 2; dat.category = "B"; dat.Val = 6; dlist.Add(dat); dat.Year = 2016; dat.Month = "Jan"; dat.MonthOrder = 1; dat.category = "A"; dat.Val = 4; dlist.Add(dat); dat = new DataCls(); dat.Year = 2016; dat.Month = "Feb"; dat.MonthOrder = 2; dat.category = "A"; dat.Val = 7; dlist.Add(dat); dat = new DataCls(); dat.Year = 2016; dat.Month = "Feb"; dat.MonthOrder = 2; dat.category = "B"; dat.Val = 8; dlist.Add(dat); dat = new DataCls(); dat.Year = 2016; dat.Month = "Feb"; dat.MonthOrder = 2; dat.category = "C"; dat.Val = 9; dlist.Add(dat); dat = new DataCls(); dat.Year = 2015; dat.Month = "Mar"; dat.MonthOrder = 3; dat.category = "A"; dat.Val = 10; dlist.Add(dat);

输出我期待。

{ "Series": [{ "name": "A", "stack": 2015, "data": [1, 5, 10] }, { "name": "B", "stack": 2015, "data": [2, 6, null] }, { "name": "C", "stack": 2015, "data": [3, null, null] }, { "name": "A", "stack": 2016, "data": [4, 7, null] }, { "name": "B", "stack": 2016, "data": [null,8, null] }, { "name": "C", "stack": 2016, "data": [null,9, null] }] }

要记住的要点：

Year属性将分配给json对象中的stack属性。 Category属性将分配给json对象中的name属性。 Category的数量不固定。给定Category可能会或可能不会出现给定月份。 val属性将被分配给每个月的json对象中的data数组。如果一个月缺少类别，我们需要将该值视为null。

我真的不确定如何解决这个问题。我该如何解决这个问题。我很难解决这个问题。

Class:

public class DataCls { public int Year { get; set; } public string Month { get; set; } public int MonthOrder { get; set; } public string category { get; set; } public int Val { get; set; } }

Data:

List<DataCls> dlist = new List<DataCls>(); DataCls dat = new DataCls(); dat.Year = 2015; dat.Month = "Jan"; dat.MonthOrder = 1; dat.category = "A"; dat.Val = 1; dlist.Add(dat); dat = new DataCls(); dat.Year = 2015; dat.Month = "Jan"; dat.MonthOrder = 1; dat.category = "B"; dat.Val = 2; dlist.Add(dat); dat = new DataCls(); dat.Year = 2015; dat.Month = "Jan"; dat.MonthOrder = 1; dat.category = "C"; dat.Val = 3; dlist.Add(dat); dat = new DataCls(); dat.Year = 2015; dat.Month = "Feb"; dat.MonthOrder = 2; dat.category = "A"; dat.Val = 5; dlist.Add(dat); dat = new DataCls(); dat.Year = 2015; dat.Month = "Feb"; dat.MonthOrder = 2; dat.category = "B"; dat.Val = 6; dlist.Add(dat); dat.Year = 2016; dat.Month = "Jan"; dat.MonthOrder = 1; dat.category = "A"; dat.Val = 4; dlist.Add(dat); dat = new DataCls(); dat.Year = 2016; dat.Month = "Feb"; dat.MonthOrder = 2; dat.category = "A"; dat.Val = 7; dlist.Add(dat); dat = new DataCls(); dat.Year = 2016; dat.Month = "Feb"; dat.MonthOrder = 2; dat.category = "B"; dat.Val = 8; dlist.Add(dat); dat = new DataCls(); dat.Year = 2016; dat.Month = "Feb"; dat.MonthOrder = 2; dat.category = "C"; dat.Val = 9; dlist.Add(dat); dat = new DataCls(); dat.Year = 2015; dat.Month = "Mar"; dat.MonthOrder = 3; dat.category = "A"; dat.Val = 10; dlist.Add(dat);

output I am expecting.

{ "Series": [{ "name": "A", "stack": 2015, "data": [1, 5, 10] }, { "name": "B", "stack": 2015, "data": [2, 6, null] }, { "name": "C", "stack": 2015, "data": [3, null, null] }, { "name": "A", "stack": 2016, "data": [4, 7, null] }, { "name": "B", "stack": 2016, "data": [null,8, null] }, { "name": "C", "stack": 2016, "data": [null,9, null] }] }

points to remember:

Year property will be assigned to stack property in json object. Category property will be assigned to name property in json object. The number of Category is not fixed. A given Category may or may not be present for a given month. val property will be assigned to data array in json object for each month. If the category is missing for a month, we need to consider the value as null.

I am really not sure how to go about this problem. How can I proceed to solve this problem. I am having hard time fixing this.

最满意答案

您可以使用linq的组合来转换数据，然后使用Json.NET来创建json。

//here assuming monthMap is an int map // "Jan" => 1 // "Feb" => 2 // etc var lqOutput = dbOutput .OrderBy(a=>monthMap[a.Month]) .GroupBy(a=>new{a.Category,a.Year}) .Select(a=>new{ name=a.Key.Category, stack=a.Key.Year, data = a.Select(b=>b.Val).ToArray() }); var json = JsonConvert.SerializeObject(new{ Series = lqOutput});

如果你想让没有出现的月份被跳过，你可以制作一个更小的辅助方法

如果你这样做，你需要将1月映射到0，从2月到1，等等

You can use a combination of linq to transform the data and then Json.NET to create the json.

//here assuming monthMap is an int map // "Jan" => 1 // "Feb" => 2 // etc var lqOutput = dbOutput .OrderBy(a=>monthMap[a.Month]) .GroupBy(a=>new{a.Category,a.Year}) .Select(a=>new{ name=a.Key.Category, stack=a.Key.Year, data = a.Select(b=>b.Val).ToArray() }); var json = JsonConvert.SerializeObject(new{ Series = lqOutput});

If you wanted to have the months that do not show up be skipped you could make a smaller helper method

If you do this you need to map January to 0, Feb to 1, etc

更多推荐