title: Create Recursive Linq to Entities query? (Like recursive CTE)
body: <p>I have a web application with simple <strong>tree structure of usergroups</strong> and looking for a way to query for such things as:</p>
  
  <ul>
  <li>top level parent</li>
  <li>all child groups at any level(child of childs and etc)</li>
  <li>all parent groups (my parent, parent of parent etc)</li>
  </ul>
  
  <p>I'm using MS SQL so its not a big problem to write selections I need in DB and to save them as stored procedures.</p>
  
  <p><strong>But is there a way to create such query with using just EntityToSql?</strong></p>
  
  <p>Here is example of the TSQL query I use:</p>
  
  <pre><code>DECLARE @userGroupId uniqueidentifier 
  DECLARE @searchTerm nvarchar(20)
  
  SET @userGroupId = '00000000-0000-0000-0000-000000000000'
  Set @searchTerm = 'test'
  
  ;WITH n(lvl,id,ParentUserGroupId,FullName) AS (
      SELECT 1,id,ParentUserGroupId,FullName FROM UserGroups where 
      id in (select UserGroupId 
      FROM Users WHERE login like '%'+@searchTerm+'%')
      UNION ALL 
      SELECT n.lvl+1, nplus1.id,nplus1.ParentUserGroupId, nplus1.FullName
      FROM UserGroups as nplus1,n WHERE n.ParentUserGroupId = nplus1.id
  )
  SELECT DIStinct id,FullName 
  FROM n where ParentUserGroupId = @userGroupId OR
  ((@userGroupId IS NULL OR @userGroupId = '00000000-0000-0000-0000-000000000000')
  AND ParentUserGroupId IS NULL)
  </code></pre>