转换从平坦化SQL Server的分层数据变成结构化的JSON对象用C＃/ LINQ的 [英] Converting flattened hierarchical data from SQL Server into a structured JSON object with C#/Linq

问题描述

我开发一个MVC应用程序，从SQL Server中的表的结构，像这样检索数据：

I am developing an MVC app that retrieves data from a table in SQL Server that is structured like so:

+-----------------------------------+
| Id | Name   | Hierarchy   | Depth |
|-----------------------------------|
| 01 | Justin | /           |     0 |
| 02 | Chris  | /1          |     1 |
| 03 | Beth   | /1/1        |     2 |
+-----------------------------------+

在层次列中的示例数据是字符串重的 HIERARCHYID 数据类型的presentation和在深度列使用 HIERARCHYID :: GetLevel（）法计算。

The example data in the Hierarchy column is the string representation of the hierarchyid datatype, and the Depth column is computed using the hierarchyid::GetLevel() method.

，我制订上表中这个类：

Using Entity Framework 4.1, I have mapped the above table to this class:

public class Node {
    public int Id { get; set; }
    public string Name { get; set; }
    public string HierarchyPath { get; set; } // String representation of the hierarchyid
    public int Depth { get; set; }
}

我想用这个信息使用 JS可视化工具包<中显示的层次的图形重新presentation用户/一>，它要求将构成的数据：

I want to use this information to display a graphical representation of the hierarchy to the user using the JS Visualizations Toolkit, which requires the data to be structured:

var node = {
    id: 1,
    name: 'Justin'
    children: [{
        id: 2,
        name: 'Chris',
        children: [{
            id: 3,
            name: 'Beth',
            children: []
        }]
    }]
}

我无法发展的逻辑我的型号列表转换成结构化的JSON对象。有什么建议？

I'm having trouble developing the logic to convert a list of my models into a structured JSON object. Any suggestions?

推荐答案

编辑：我没有时间来修复低于现在的答案，但鉴于问题的额外信息，我怀疑你想保留一个词典＆LT; INT，HierarchicalNode＆GT; 而非列表＆LT; HierarchicalNode＆GT; 让你不依赖于任何顺序..

I don't have time to fix the answer below right now, but given the extra information in the question, I suspect you want to keep a Dictionary<int, HierarchicalNode> rather than a List<HierarchicalNode> so that you're not relying on any ordering...

我就忘记了JSON重新presentation入手，集中精力建设层次的内存POCO再presentation。要做到这一点，我会使用这样的：

I would forget about the JSON representation to start with, and concentrate on building an in-memory POCO representation of the hierarchy. To do that, I'd use something like this:

class HierarchicalNode
{
    private readonly List<HierarchicalNode> children =
        new List<HierarchicalNode>();        
    public List<HierarchicalNode> Children { get { return children; } }

    private readonly string name;
    public string Name { get { return name; } }

    private readonly int id;
    public int Id { get { return id; } }

    public HierarchicalNode(string name, int id)
    {
        this.name = name;
        this.id = id;
    }
}

然后建立了树是这样的：

Then build up the tree like this:

// Make sure we get everything in a sensible order, parents before children
var query = context.Nodes.OrderBy(x => x.Depth);

var root = new HierarchicalNode("Root", 0);
foreach (var node in query)
{       
    var current = root;
    foreach (string part = node.HierarchyPath.Split(new[] {'/'},
                                   StringSplitOptions.RemoveEmptyEntries))
    {
        int parsedPart = int.Parse(part);
        current = current.Children[parsedPart - 1];
    }
    current.Children.Add(new HierarchicalNode(node.Name, node.Id));
}

这篇关于转换从平坦化SQL Server的分层数据变成结构化的JSON对象用C＃/ LINQ的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！