title: How to optimize LINQ-to-SQL for recursive queries?
body: <p>I have the following SQL table:</p>
  
  <pre><code> ObjectTable
  --------------------------------------------------
  |  ID      |  Name         |  Order  |  ParentID  |
  |  int PK  |  nvarchar(50) |  int    |  int FK    |     
  </code></pre>
  
  <p><code>ObjectTable.ParentID</code> is a nullable field with a relationship to a different Object record's ID.  LINQ-to-SQL generates an class that looks like:</p>
  
  <pre><code> public class DbObject{
  
        int ID { get; set; }
  
        string Name { get; set; }
  
        int Order { get; set; }
  
        int? ParentID { get; set; }
  
        DbObject Parent { get; set; }
  
        EntitySet&lt;DbObject&gt; ChildObjects { get; set; }
  
   }
  </code></pre>
  
  <p>When I load a <code>DbObject</code> instance, I need to be able to recursively access the child objects, so that I can write the data to a hierarchical JSON object.  </p>
  
  <ul>
  <li><p>Will I execute a separate query everytime I access the elements via <code>DbObject.ChildObjects</code>?  Since DB transactions take the longest, it seems like this is a very inefficient way to load a recursive hierarchy.  </p></li>
  <li><p>What is the best practice for executing a recursive query with LINQ-to-SQL and/or EF? </p></li>
  <li><p><strike>Is LINQ-to-SQL integrated with <a href="http://msdn.microsoft.com/en-us/library/ms186243.aspx" rel="nofollow noreferrer">Common Table Expressions</a>?</strike> 
  <em>I found <a href="https://stackoverflow.com/questions/584841/common-table-expression-cte-in-linq-to-sql">Common Table Expression (CTE) in linq-to-sql?</a></em></p></li>
  </ul>