title: How to get a hierarchical data in Entity Framework or in SQL Server
body: <p>I have the following tables,</p>
  
  <p><em>TblFaq (a record represents a diagram), PK: FAQId, FK: FAQWebPageId</em></p>
  
  <p><a href="https://i.stack.imgur.com/83Ank.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/83Ank.png" alt="TblFAQ"></a></p>
  
  <p><em>TblFaqNode (has all nodes for all the diagrams), PK: NodeId, FK: FAQId</em></p>
  
  <p><a href="https://i.stack.imgur.com/4XkMy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4XkMy.png" alt="TblFAQNode"></a></p>
  
  <p><em>TblFaqNodeMapping (has information about which node is connected to which node), PK: FAQNodeMapId, FK: SourceNodeId [parent], DestNodeId [child]</em></p>
  
  <p><a href="https://i.stack.imgur.com/JPB8f.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JPB8f.png" alt="TblFAQNodeMapping"></a></p>
  
  <p>I need to form a C# object as shown in the JSON below, by either using Entity Framework LINQ to SQL query (Database First Approach) or by writing a stored procedure to return JSON/XML.</p>
  
  <p>I'm not looking for a result set which says which node connects to which one because again I've to loop all the records recursively to create the C# object which is not efficient and as of now I'm only able to do this, as shown in below query.</p>
  
  <pre><code>SELECT
      FAQ.FAQWebPageId,
      FAQ.FAQId,
      FAQ.Name,
      FAQNode.NodeId,
      FAQNode.[Data],
      FAQNode.FAQNodeTypeId,
      FAQNode.FAQNodeBranchTypeId,
      FAQNode.IsStartNode,
      FAQNode.IsEndNode,
      FAQNodeMap.FAQNodeMapId,
      FAQNodeMap.SourceNodeId,
      FAQNodeMap.DestNodeId,
      FAQNodeMap.Label
  FROM TBL_LY_FAQ AS FAQ
  JOIN TBL_LY_FAQNode AS FAQNode
  ON FAQ.FAQId = FAQNode.FAQId
  LEFT JOIN TBL_LY_FAQNodeMap AS FAQNodeMap
  ON FAQNode.NodeId = FAQNodeMap.DestNodeId
  WHERE FAQ.FAQWebPageId = 1
  ORDER BY FAQNode.NodeId
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/uS5wB.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/uS5wB.png" alt="Simple query to get parent-child relationship"></a></p>
  
  <p>Also, the nodes in <em>TblFaqNode</em> which are having <em>IsStartNode</em> as 1 should always be the top nodes in the result i.e. nodes not having any parent and the whole result set will be filtered by the where condition for <em>FAQWebPageId</em>.</p>
  
  <p>Directly through the database query I want to get the C# object in the hierarchical format as shown in the below JSON, Please suggest how I can achieve this.</p>
  
  <p>Thanks in advance.</p>
  
  <pre><code>[
     {
        "FAQWebPageId":1,
        "FAQId":1,
        "FAQName":"CareersFAQ",
        "NodeId":1,
        "Data":"Do you have any openings for BA",
        "FAQNodeTypeId":1,
        "FAQNodeBranchTypeId":3,
        "IsStartNode":true,
        "IsEndNode":false,
        "FAQNodeMapId":0,
        "SourceNodeId":0,
        "DestNodeId":0,
        "Label":null,
        "Adjacents":[
           {
              "FAQWebPageId":1,
              "FAQId":1,
              "FAQName":"CareersFAQ",
              "NodeId":2,
              "Data":"yes we have an opening, it requires 2 + years in product development",
              "FAQNodeTypeId":2,
              "FAQNodeBranchTypeId":1,
              "IsStartNode":false,
              "IsEndNode":false,
              "FAQNodeMapId":1,
              "SourceNodeId":1,
              "DestNodeId":2,
              "Label":"Technical BA",
              "Adjacents":[
                 {
                    "FAQWebPageId":1,
                    "FAQId":1,
                    "FAQName":"CareersFAQ",
                    "NodeId":20,
                    "Data":"What are the services offered by your company",
                    "FAQNodeTypeId":1,
                    "FAQNodeBranchTypeId":1,
                    "IsStartNode":false,
                    "IsEndNode":false,
                    "FAQNodeMapId":29,
                    "SourceNodeId":2,
                    "DestNodeId":20,
                    "Label":null,
                    "Adjacents":[
                       {
                          "FAQWebPageId":1,
                          "FAQId":1,
                          "FAQName":"CareersFAQ",
                          "NodeId":8,
                          "Data":"We offer services in HR, LMS, IT and staffing",
                          "FAQNodeTypeId":2,
                          "FAQNodeBranchTypeId":1,
                          "IsStartNode":false,
                          "IsEndNode":true,
                          "FAQNodeMapId":28,
                          "SourceNodeId":20,
                          "DestNodeId":8,
                          "Label":null,
                          "Adjacents":[
  
                          ]
                       }
                    ]
                 }
              ]
           },
           {
              "FAQWebPageId":1,
              "FAQId":1,
              "FAQName":"CareersFAQ",
              "NodeId":3,
              "Data":"yes we do have, Financial BA requires 5 years experience",
              "FAQNodeTypeId":2,
              "FAQNodeBranchTypeId":1,
              "IsStartNode":false,
              "IsEndNode":false,
              "FAQNodeMapId":2,
              "SourceNodeId":1,
              "DestNodeId":3,
              "Label":"Financial BA",
              "Adjacents":[
                 {
                    "FAQWebPageId":1,
                    "FAQId":1,
                    "FAQName":"CareersFAQ",
                    "NodeId":4,
                    "Data":"Does your experience match ?",
                    "FAQNodeTypeId":3,
                    "FAQNodeBranchTypeId":2,
                    "IsStartNode":false,
                    "IsEndNode":false,
                    "FAQNodeMapId":3,
                    "SourceNodeId":3,
                    "DestNodeId":4,
                    "Label":null,
                    "Adjacents":[
                       {
                          "FAQWebPageId":1,
                          "FAQId":1,
                          "FAQName":"CareersFAQ",
                          "NodeId":5,
                          "Data":"Please apply at our careers page",
                          "FAQNodeTypeId":2,
                          "FAQNodeBranchTypeId":1,
                          "IsStartNode":false,
                          "IsEndNode":true,
                          "FAQNodeMapId":4,
                          "SourceNodeId":4,
                          "DestNodeId":5,
                          "Label":"Yes",
                          "Adjacents":[
  
                          ]
                       },
                       {
                          "FAQWebPageId":1,
                          "FAQId":1,
                          "FAQName":"CareersFAQ",
                          "NodeId":6,
                          "Data":"no problem, you can still share us your profile by registering at our careers page, we will contact you if any match in future",
                          "FAQNodeTypeId":2,
                          "FAQNodeBranchTypeId":1,
                          "IsStartNode":false,
                          "IsEndNode":true,
                          "FAQNodeMapId":5,
                          "SourceNodeId":4,
                          "DestNodeId":6,
                          "Label":null,
                          "Adjacents":[
  
                          ]
                       }
                    ]
                 }
              ]
           }
        ]
     }
  ]
  </code></pre>