Converting flattened hierarchical data from SQL Server into a structured JSON object with C#/Linq

by

EDIT: 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…

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[] {"https://stackoverflow.com/"},
                                   StringSplitOptions.RemoveEmptyEntries))
    {
        int parsedPart = int.Parse(part);
        current = current.Children[parsedPart - 1];
    }
    current.Children.Add(new HierarchicalNode(node.Name, node.Id));
}

More Related Contents:

  1. Why LINQ to Entities does not recognize the method ‘System.String ToString()?
  2. LINQ to Entities does not recognize the method ‘System.Web.Mvc.FileResult’
  3. Entity Framework Include OrderBy random generates duplicate data
  4. How do I write unencoded Json to my View using Razor?
  5. One DbContext per request in ASP.NET MVC (without IOC container)
  6. How can I supply an AntiForgeryToken when posting JSON data using $.ajax?
  7. using stored procedure in entity framework
  8. ASP.NET MVC3 and Entity Framework Code first architecture
  9. Filter/Search using Multiple Fields – ASP.NET MVC
  10. Where can I find a list of escape characters required for my JSON ajax return type?
  11. How can I return camelCase JSON serialized by JSON.NET from ASP.NET MVC controller methods?
  12. Return a JSON string explicitly from Asp.net WEBAPI?
  13. MVC: How to Return a String as JSON
  14. Json and Circular Reference Exception
  15. ASP.NET MVC: Controlling serialization of property names with JsonResult
  16. Saving Many to Many relationship data on MVC Create view
  17. MVC controller : get JSON object from HTTP body?
  18. How to group by on 2 child entities and get total of both this child entities?
  19. Defining multiple Foreign Key for the Same table in Entity Framework Code First
  20. ‘Microsoft.SqlServer.Types’ version 10 or higher could not be found on Azure
  21. How to return Json object from MVC controller to view
  22. The operation cannot be completed because the DbContext has been disposed using MVC 4
  23. Force lowercase property names from Json() in ASP.NET MVC
  24. Use Entity framework I want to include only first children objects and not child of child(sub of sub)
  25. What ‘sensitive information’ could be disclosed when setting JsonRequestBehavior to AllowGet
  26. Only primitive types or enumeration types are supported in this context
  27. The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value
  28. The type or namespace name ‘DbContext’ could not be found [closed]
  29. ASP.NET MVC – Combine Json result with ViewResult
  30. Strongly-Typed ASP.NET MVC with ADO.NET Entity Framework [closed]

Leave a Comment