Common Table Expression (CTE) in linq-to-sql?

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.

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.

Here's an example of what I'm doing in SQL that I'm wondering if I can do in Linq to SQL:

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]

AFAIK, this isn't supported by the object model. However, LINQ supports a method to execute a query (strangly enough called DataContext.ExecuteQuery). Looks like you can use that to call a arbitrary piece of SQL and map it back to LINQ. You won't be SQL free because of the embedded SQL, but you won't have to use a sproc.

How to: Directly Execute SQL Queries (LINQ to SQL)

Common Table Expression (CTE) in linq-to-sql?

Related

Recent Posts