Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Common Table Expression in EntityFramework

I have this query in Sql Server which I need to consume in EntityFramework, So how can I write a EntityFramwork code which 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 in 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 same, as recursive as CTE.

like image 877
Kishore Kumar Avatar asked Nov 23 '12 19:11

Kishore Kumar


People also ask

What does CTE do in SQL?

The common table expression (CTE) is a powerful construct in SQL that helps simplify a query. CTEs work as virtual tables (with records and columns), created during the execution of a query, used by the query, and eliminated after query execution.

What is include in Entityframework?

Entity Framework Classic Include The Include method lets you add related entities to the query result. In EF Classic, the Include method no longer returns an IQueryable but instead an IncludeDbQuery that allows you to chain multiple related objects to the query result by using the AlsoInclude and ThenInclude methods.

What is CTE C#?

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.


1 Answers

EF does not support recursive CTE's. Use a view or a table valued function.

like image 158
usr Avatar answered Sep 22 '22 02:09

usr