代码之家 › 专栏 › 技术社区 › OpenStack

无法将数据正确转换为层次结构

datatable c#-4.0 linq .net c#

OpenStack · 技术社区 · 7 年前

我有两个数据表。1包含数据,其他包含行之间的关系信息。代码似乎工作正常,但缺少一些节点。我知道问题的原因,但不知道如何解决问题。

包含数据的DataTable:

DataTable dataTable = new DataTable();
dataTable.Columns.Add("Name");
dataTable.Columns.Add("America");
dataTable.Columns.Add("Japan");
dataTable.Columns.Add("Singapore");

dataTable.Rows.Add("A", 200, 100, 300);
dataTable.Rows.Add("B", 300, 300, 600);
dataTable.Rows.Add("C", 400, 400, 700);
dataTable.Rows.Add("D", 500, 500, 800);
dataTable.Rows.Add("E", 600, 600, 900);
dataTable.Rows.Add("F", 700, 700, 1000);
dataTable.Rows.Add("G", 800, 800, 600);
dataTable.Rows.Add("H", 900, 100, 400);
dataTable.Rows.Add("I", 100, 200, 300);
dataTable.Rows.Add("J", 200, 300, 200);
dataTable.Rows.Add("K", 300, 500, 500);
dataTable.Rows.Add("L", 200, 300, 200);
dataTable.Rows.Add("M", 300, 500, 500);

映射数据表

DataTable mappingTable = new DataTable();
mappingTable.Columns.Add("Name", typeof(string));
mappingTable.Columns.Add("id", typeof(int));
mappingTable.Columns.Add("parentID", typeof(int));

mappingTable.Rows.Add("A", 1, 0);
mappingTable.Rows.Add("B", 2, 1);
mappingTable.Rows.Add("C", 3, 1);
mappingTable.Rows.Add("D", 4, 0);
mappingTable.Rows.Add("E", 5, 4);
mappingTable.Rows.Add("F", 6, 0);
mappingTable.Rows.Add("G", 7, 6);
mappingTable.Rows.Add("H", 8, 6);
mappingTable.Rows.Add("I", 9, 1);
mappingTable.Rows.Add("J", 10, 0);
mappingTable.Rows.Add("K", 11, 10);
mappingTable.Rows.Add("L", 12, 0);
mappingTable.Rows.Add("M", 13, 0);

converion代码

var data = dataTable.Rows.Cast<DataRow>()
    .Select(r => dataTable.Columns.Cast<DataColumn>().ToDictionary(c => c.ColumnName, c => r[c.ColumnName]))
    .ToList();
ILookup<int, int> mapping;
mapping = mappingTable.Rows.Cast<DataRow>()
    .Where(r => !r["parentID"].Equals(0))
    .ToLookup(r => (int)r["parentID"], r => (int)r["id"]);

var output = new List<Dictionary<string, Object>>();
foreach (var group in mapping)
{
    data[@group.Key - 1].Add("children", @group.Select(c => data[c - 1]).ToList());
    output.Add(data[@group.Key - 1]);
}

var json = Newtonsoft.Json.JsonConvert.SerializeObject(output);

缺少节点(L&M)父id为0的节点,因为该组已创建。

 {
        "Name": "L",
        "America": "200",
        "Japan": "300",
        "Singapore": "200"

    }, {
        "Name": "M",
        "America": "200",
        "Japan": "300",
        "Singapore": "200"

    }

电流输出:

[{
        "Name": "A",
        "America": "200",
        "Japan": "100",
        "Singapore": "300",
        "children": [{
                "Name": "B",
                "America": "300",
                "Japan": "300",
                "Singapore": "600"
            }, {
                "Name": "C",
                "America": "400",
                "Japan": "400",
                "Singapore": "700"
            }, {
                "Name": "I",
                "America": "100",
                "Japan": "200",
                "Singapore": "300"
            }
        ]
    }, {
        "Name": "D",
        "America": "500",
        "Japan": "500",
        "Singapore": "800",
        "children": [{
                "Name": "E",
                "America": "600",
                "Japan": "600",
                "Singapore": "900"
            }
        ]
    }, {
        "Name": "F",
        "America": "700",
        "Japan": "700",
        "Singapore": "1000",
        "children": [{
                "Name": "G",
                "America": "800",
                "Japan": "800",
                "Singapore": "600"
            }, {
                "Name": "H",
                "America": "900",
                "Japan": "100",
                "Singapore": "400"
            }
        ]
    }
]

预期输出:添加缺少的节点

2 回复 | 直到 7 年前

NetMage 7 年前

下面是我对循环遍历所有父对象并添加其子对象,然后将其添加到输出的更改:

var data = dataTable.Rows.Cast<DataRow>()
                    .Select(r => dataTable.Columns.Cast<DataColumn>().ToDictionary(c => c.ColumnName, c => r[c.ColumnName]))
                    .ToList();

var mapping = mappingTable.Rows.Cast<DataRow>()
                          .Where(r => !r["parentID"].Equals(0))
                          .ToLookup(r => (int)r["parentID"], r => (int)r["id"]);

var output = new List<Dictionary<string, Object>>();
foreach (var parent in mappingTable.Rows.Cast<DataRow>().Where(r => r["parentID"].Equals(0))) {
    var parentID = (int)parent["id"];

    if (mapping.Contains(parentID))
        data[parentID-1].Add("children", mapping[parentID].Select(c => data[c-1]).ToList());
    output.Add(data[parentID-1]);
}

Lennart Stoop 7 年前

就我个人而言,我更喜欢先创建一个模型,这样以后创建树就容易多了。

型号:

    public class DataModel
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int America { get; set; }
        public int Japan { get; set; }
        public int Singapore { get; set; }
        public IList<DataModel> Children { get; set; }

        public DataModel()
        {
            Children = new List<DataModel>();
        }
    }

    public class DataMapping
    {
        public string Name { get; set; }
        public int Id { get; set; }
        public int ParentId { get; set; }
    }

映射数据:

        var mappings = mappingTable.Rows.Cast<DataRow>()
            .Select(
                r => new DataMapping
                {
                    Name = r.Field<string>("Name"),
                    Id = r.Field<int>("id"),
                    ParentId = r.Field<int>("parentID"),
                }).ToList();

        var data = dataTable.Rows.Cast<DataRow>()
            .Select(
                r => new DataModel
                {
                    Name = r.Field<string>("Name"),
                    America = Convert.ToInt32(r.Field<string>("America")),
                    Japan = Convert.ToInt32(r.Field<string>("Japan")),
                    Singapore = Convert.ToInt32(r.Field<string>("Singapore")),
                    Id = mappings.Single(m => m.Name.Equals(r.Field<string>("Name"))).Id
                }).ToList();

创建树:

        foreach (var mapping in mappings.Where(m => m.ParentId != 0))
        {
            var parent = data.Single(d => d.Id == mapping.ParentId);
            var child = data.Single(d => d.Id == mapping.Id);
            parent.Children.Add(child);
        }

输出:

        var json = Newtonsoft.Json.JsonConvert.SerializeObject(data);