select id,name,DepartmentID, 0 AS level
where id =2
select Department.ID,Department.name,Department.DepartmentID, level+1
inner join cte1 on Department.ID=cte1.DepartmentID
select * from cte1
These posts are similar to your question.please see these:
I think there is no way to write a single LINQ to SQL query that could get all 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.
See this post: common-table-expression-in-entityframework