根据某些规则将数据表转换为 JSON

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

数据：

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

要记住的要点：

1. Year属性将分配给 JSON 对象中的stack属性。
2. Category属性将分配给 JSON 对象中的name属性。
3. Category的数量不是固定的。
4. 给定的Category在给定的月份可能存在，也可能不存在。
5. val属性将分配给每个月data JSON 对象中的数组。
6. 如果类别缺失一个月，我们需要将该值视为 null。

我真的不知道如何处理这个问题。我怎样才能继续解决这个问题。 我很难解决这个问题。

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

var dictToArr = new Func<int,Dictionary<int,object>, object[]>((size,d)=>{
        var retv = new object[size];
        foreach(var key in d.Keys)
        {
            if(key>=0)
            {
                retv[key] = d[key];
            }
        }
        return retv;
    });
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 = dictToArr ( 12,
                                                a.Select(b=>new{ 
                                                            val= b.Val, 
                                                            idx=monthMap[b.Month]
                                                  }).ToDictionary(
                                                              b=>b.idx,
                                                              b=>b.val as object
                                                  ) )
                                  });
var json = JsonConvert.SerializeObject(new{ Series = lqOutput});

public static List<T> DataReaderMapToList<T>(this IDataReader dr)
    {
        var list = new List<T>();
        var obj = default(T);
        var columns = dr.GetSchemaTable().Rows;
        var columnsArray = new DataRow[columns.Count];
        columns.CopyTo(columnsArray, 0);
        var columnNames = columnsArray.Select(x => x[0].ToString());
        while (dr.Read())
        {
            obj = Activator.CreateInstance<T>();
            var type = obj.GetType();
            foreach (var column in columnNames)
            {
                var field = type.GetField(column);
                if (field != null && dr[column].GetType().FullName == field.FieldType.FullName)
                {
                    field.SetValue(obj, dr[column], BindingFlags.SetField, null, null);
                    continue;
                }
                var property = type.GetProperty(column);
                if (property != null && dr[column].GetType().FullName == property.PropertyType.FullName)
                {
                    property.SetValue(obj, dr[column]);
                }
            }
            list.Add(obj);
        }
        return list;
    }