title: How to formulate an IQueryable to query a recursive database table?
body: <p>I have a database table like this:</p>
  
  <pre><code>Entity
  ---------------------
  ID        int      PK
  ParentID  int      FK
  Code      varchar
  Text      text
  </code></pre>
  
  <p>The <code>ParentID</code> field is a foreign key with another record in the same table (recursive). So the structure represents a Tree.</p>
  
  <p>I'm trying to write a method to query this table and get 1 specific Entity based on a path. A path would be a string representing the <code>Code</code> properties of the Entity and the parent Entities. So an example path would be <code>"foo/bar/baz"</code> which means the one specific Entity of which the <code>Code == "baz"</code>, the parent's <code>Code == "bar"</code> and the parent of the parent's <code>Code == "foo"</code>.</p>
  
  <p>My attempt:</p>
  
  <pre><code>public Entity Single(string path)
  {
   string[] pathParts = path.Split('/');
   string code = pathParts[pathParts.Length -1];
  
   if (pathParts.Length == 1)
    return dataContext.Entities.Single(e =&gt; e.Code == code &amp;&amp; e.ParentID == 0);
  
   IQueryable&lt;Entity&gt; entities = dataContext.Entities.Where(e =&gt; e.Code == code);
   for (int i = pathParts.Length - 2; i &gt;= 0; i--)
   {
    string parentCode = pathParts[i];
    entities = entities.Where(e =&gt; e.Entity1.Code == parentCode); // incorrect
   }
  
   return entities.Single();
  }
  </code></pre>
  
  <p>I know this isn't correct because the <code>Where</code> inside the <code>for</code>loop just adds more conditions to the <strong>current Entity</strong> instead of the <strong>parent Entity</strong>, but how do I correct this? In words I would like the for-loop to say "and the parent's code must be x and the parent of that parent's code must be y, and the parent of that parent of that parent's code must be z .... etc". Besides that, for performance reasons I'd like it to be one IQueryable so there will be just 1 query going to the database.</p>