title: Writing Recursive CTE using Entity Framework Fluent syntax or Inline syntax
body: <p>I am new to this <strong>recursion</strong> in both SQL and Entity Framework (ADO.NET Entity Mapping). I am working on a comment management where I have a <code>Comments</code> table and the table contains columns <code>NewsID, CommentID, ParentCommentID, IndentLevel, CreatedTime</code>. </p>
  
  <p>I am trying to get a list of comments for a particular news item where all the comments are arranged according to the child under parent and created time, as shown below:</p>
  
  <pre><code>CommentID | time | ParentCommentID
  Guid1     |  t1  | null
  Guid4     |  t4  | Guid1
  Guid2     |  t2  | null
  Guid3     |  t3  | Guid2
  </code></pre>
  
  <p>Priority has to be given to the child parent relationship and then the created time. </p>
  
  <p>What I have leaned so far is (from internet resources and previous stackoverflow Q/A)</p>
  
  <ul>
  <li>As illustrated these recursive queries are slow. and doing this using Entity Framework is even slower. But it can be achieved.</li>
  <li>So, It can be done by creating a stored procedure in SQL Server and calling it by using a functional import. Another thing is using Linq in Entity Framework.</li>
  <li>In SQL Server it is used in this format</li>
  </ul>
  
  <p>SQL:</p>
  
  <pre><code>WITH cte_name ( column_name [,...n] ) 
  AS 
  ( 
  CTE_query_definition –- Anchor member is defined. 
  UNION ALL 
  CTE_query_definition –- Recursive member is defined referencing cte_name. 
  ) 
  -- Statement using the CTE 
  SELECT * 
  FROM cte_name 
  </code></pre>
  
  <ul>
  <li>But before trying this I want to try the Linq.</li>
  </ul>
  
  <p>For this I have refering to this link where I have got the idea:
  <a href="https://stackoverflow.com/a/6225373/892788">https://stackoverflow.com/a/6225373/892788</a></p>
  
  <p>But I have tried to understand the code but in vain. Can someone give me a better and detailed explanation about writing recursive CTE in Entity Framework?</p>
  
  <pre><code>private IEnumerable&lt;NewsComment&gt; ArrangeComments(IEnumerable&lt;NewsComment&gt; commentsList, string parentNewsComntID, int level) 
  {
          Guid parentNewsCommentID;
          if (parentNewsComntID != null)
          {
              parentNewsCommentID = new Guid(parentNewsComntID);
          }
          else
              parentNewsCommentID = Guid.Empty;
  
          return commentsList.Where(x =&gt; x.ParentCommentID == parentNewsCommentID).SelectMany(x =&gt; new[] { x }.Concat(ArrangeComments(commentsList, x.NewsCommentID.ToString(), level + 1)); 
  
  }
  </code></pre>
  
  <p>And I am using this as below inside a method:</p>
  
  <pre><code>return ArrangeComments(commentList,null , 0);
  </code></pre>
  
  <p>I have tried them and it seems I am getting nowhere. Though there are explanations on the SQL recursion there are less examples for Linq and are vague for me due to less familiarity.  Can somebody help me to understand this CTE recursion in Linq that is great</p>
  
  <p>Thanks in advance</p>