Convert SQL to LINQ to SQL

Question

I have the SQL query

with c as (
  select  categoryId,parentId, name,0 as [level]
  from task_Category b 
  where b.parentId is null
    union all
  select b.categoryId,b.parentId,b.name,[level] + 1
  from task_Category b join c on b.parentId = 
    c.categoryId)
select name,[level],categoryId,parentId 
  as item 
from c

and I want to convert it to LINQ to SQL, yet my LINQ skills are not there yet. Could someone please help me convert this. It's the with and union statements that are making this a bit more complex for me.

Any help appreciated.

Answer 1

That is a recursive CTE. LINQ to SQL does not support recursive CTEs yet (and probably never will). Sorry!

As a workaround you can leave the query as it is in SQL and put it in a stored procedure. You can then call that stored procedure from LINQ to SQL.

Answer 2

LINQ-to-SQL only supports basic queries; CTE and recursion are not included in this. Therefore, here's my high-tech translation to LINQ-to-SQL:

var data = ctx.ExecuteQuery<MyStub>(@"
with c as (
  select  categoryId,parentId, name,0 as [level]
  from task_Category b 
  where b.parentId is null
    union all
  select b.categoryId,b.parentId,b.name,[level] + 1
  from task_Category b join c on b.parentId = 
    c.categoryId)
select name,[level],categoryId,parentId 
  as item 
from c").ToList();

with

class MyStub {
    public string name {get;set;}
    public int level {get;set;}
    public int categoryId {get;set;}
    public int parentId {get;set;}
}