Ali Soltani Ali Soltani - 1 month ago 13
C# Question

Get parent department node in Entity Framework

I have a SQL table like this:

myDepartment

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

tree

Records in tree above is:

Records

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

Output(id=4 (A1))

Two higher Level

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

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