title: Loop recursive self join table
body: <p>I have the following SQL Server table structure, it's a logical tree folder structure.</p>
  
  <p><img src="https://i.stack.imgur.com/5wgm4.png" alt="SQL Server Table Structure"></p>
  
  <p>I'm using Linq to SQL with SQL Server.</p>
  
  <p>What I want to do is to loop and create on memory the folder structure, the hierarchical folder from Root, the main parent to all childs.</p>
  
  <p><img src="https://i.stack.imgur.com/i5cKR.png" alt="Folder structure"></p>
  
  <p>I'm trying to do it with some foreach loops but I still don't get it. I think I must use some kind of recursive way.</p>
  
  <p>Some query I tried:</p>
  
  <pre><code>var flds = from folders in repo.Folders
                                     join folders2 in repo.Folders on folders.Id equals folders2.idParent
                                     select folders;
  </code></pre>
  
  <p>My goal is to iterate that folder logical structure.</p>