Common Table Expression in EntityFramework

Question

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.

score 9 · Answer 1 · answered Nov 23 '12 at 19:55

9

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

answered Nov 23 '12 at 19:55

usr

168,620
35
240
369

1

It is not Entity Framework. But it is right when EF cannot do something that you can do with sql query. – Kirill Bestemyanov Nov 23 '12 at 20:20

score 8 · Answer 2 · edited Oct 30 '18 at 18:39

8

Getting input from the other experts over SO, I have come 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
                        ");

edited Oct 30 '18 at 18:39

StayOnTarget

11,743
10
52
81

answered Nov 23 '12 at 20:20

Kishore Kumar

12,675
27
97
154

4

Why bother using EF if you're going to execute raw SQL? IMO this is rather brittle. I'd recommend turning this query into a view and adding said view to your EF model. – SouthShoreAK Aug 09 '13 at 22:21
@SouthShoreAK if `'Customer'` becomes a parameter, then this can't be converted to a view. – Erik Philips Sep 27 '16 at 21:50
2

@SouthShoreAK Because many coders prefer code-version-control within application only. Maintaining Views adds more complexity. This should be stored in a Resource String. This answer still shows mapping to a class which is handy, easier than SqlDataReader. – Kind Contributor Oct 04 '16 at 04:27

score 5 · Answer 3 · answered Nov 23 '12 at 19:54

5

You cannot use CTE recursion in Entity Framework.

answered Nov 23 '12 at 19:54

Kirill Bestemyanov

11,946
2
24
38

score 1 · Answer 4 · edited Oct 09 '16 at 10:26

1

Use stored procedure and call that stored procedure through EF

edited Oct 09 '16 at 10:26

marc_s

732,580
175
1,330
1,459

answered Apr 10 '14 at 12:53

InTheWorldOfCodingApplications

2,526
6
46
87

score 1 · Answer 5 · answered Sep 14 '17 at 14:01

1

I dont think there is support for recursive CTEs in LINQ nor in EF. The solution is to expose the CTE as a view. The article on Recursive or hierarchical queries using EF Code First and Migrations shows how to deploy such a view using EF code first migrations. Recursive or hierarchical queries using EF Code First and Migrations

Original source: https://stackoverflow.com/a/11929928/3850405

answered Sep 14 '17 at 14:01

Ogglas

62,132
37
328
418

Here is the revised location of the article mentioned: https://github.com/microsoftarchive/msdn-code-gallery-community-m-r/tree/master/Recursive%20or%20hierarchical%20queries%20using%20EF%20Code%20First%20and%20Migrations – Del Lee Jan 27 '22 at 17:09

Common Table Expression in EntityFramework

5 Answers5

Linked