Shared table expression in EntityFramework

Question

I have this query on Sql Server which I need to consume in EntityFramework. So how can I write EntityFramwork code that will have the same result as this

WITH cte AS ( SELECT * FROM StockGroups WHERE GroupParent ='Stationery' UNION ALL SELECT g.* FROM StockGroups g JOIN cte ON g.GroupParent = cte.GroupName ) SELECT * FROM cte 

I don't know how to convert it to EF, so I tried with join.

 from a in db.StockGroups join b in db.StockGroups on new { GroupParent = a.GroupParent } equals new { GroupParent = b.GroupName } where b.GroupName == "Stationery" select new { a.GroupName, a.GroupParent, Column1 = b.GroupName, Column2 = b.GroupParent } 

But the result is not as recursive as CTE.

Updated:

 IEnumerable<StockGroup> sg = dbContext.ExecuteStoreQuery<StockGroup>(@"WITH cte AS ( SELECT * FROM StockGroups WHERE GroupParent ='Stationery' UNION ALL SELECT g.* FROM StockGroups g JOIN cte ON g.GroupParent = cte.GroupName ) SELECT * FROM cte "); 

This is how I use EF right now.

Answer 1

EF does not support recursive CTEs. Use the view or table function.

Answer 2

You cannot use CTE recursion in the Entity Framework.

Answer 3

After receiving information from other experts about SO, I came up with my own way to achieve this.

 IEnumerable<StockGroup> sg = dbContext.ExecuteStoreQuery<StockGroup>( @"WITH q AS ( SELECT * FROM LedgerGroups WHERE GroupParent = 'Customers' UNION ALL SELECT m.* FROM LedgerGroups m JOIN q ON m.GroupParent = q.GroupName ) SELECT * FROM q "); 

Answer 4

Use a stored procedure and call this stored procedure through EF

Answer 5

I don't think there is support for recursive CTEs in LINQ or EF. The solution is to show the CTE as a presentation. An article on recursive or hierarchical queries using EF Code First and Migrations shows how to expand such a view using the first EF code migrations. Recursive or hierarchical queries using EF Code First and Migrations

Source Source: https://stackoverflow.com/a/416829/