Ali Soltani Ali Soltani - 1 year ago 81
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 Source

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