Get parent department node in Entity Framework

Question

I have a SQL table like this:

DepartmentID is parent of department. I've build a tree by this table(in ASP.net (C#) project):

Records in tree above is:

I need to get parents in this tree.

I can do it in SQL Server like this(for Example id=2, id is input argument):

with cte1
as
(
select id,name,DepartmentID, 0 AS level 
from Department 
where id =2
union all 
select Department.ID,Department.name,Department.DepartmentID, level+1  
from Department 
inner join cte1 on Department.ID=cte1.DepartmentID
)
select * from cte1

Output(id=2 (A))

Output(id=4 (A1))

I know EF does not support cte, but I need to get this result in EF.

It would be very helpful if someone could explain solution for this problem.

Answer 1

These posts are similar to your question.please see these:

writing-recursive-cte-using-entity-framework-fluent-syntax-or-inline-syntax
converting-sql-statement-that-contains-with-cte-to-linq

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