title: Common Table Expression (CTE) in linq-to-sql?
body: <p>Is it possible to do common table expressions (CTE) (like shown below) in Linq to SQL.  I'm pretty new to CTE's as well as Linq to SQL.</p>
  
  <p>I'm currently Stored Proc free (but not against them by any means) so i don't want to take the leap to stored procs just for one query unless it's totally necessary.</p>
  
  <p>Here's an example of what I'm doing in SQL that I'm wondering if I can do in Linq to SQL:</p>
  
  <pre><code>WITH TaskHierarchy (TaskID, [Subject], ParentID, HierarchyLevel, HierarchyPath) AS
  (
     -- Base case
     SELECT
        TaskID,
        [Subject],
        ParentID,
        1 as HierarchyLevel,
        CONVERT(VARCHAR(MAX),'/') AS HierarchyPath
     FROM Task
     WHERE TaskID = 2
  
     UNION ALL
  
     -- Recursive step
     SELECT
        t.TaskID,
        t.Subject,
        t.ParentID,
        th.HierarchyLevel + 1 AS HierarchyLevel,
        CONVERT(varchar(MAX),th.HierarchyPath + CONVERT(VARCHAR(32),t.ParentID) + '/') AS HierarchyPath
     FROM Task t
        INNER JOIN TaskHierarchy th ON
           t.ParentID = th.TaskID
  )
  
  SELECT *
  FROM TaskHierarchy
  ORDER BY HierarchyLevel, [Subject]
  </code></pre>