公共表前pression中的EntityFramework [英] Common Table Expression in EntityFramework

问题描述

我有这个疑问在SQL服务器，我需要消耗的EntityFramework，那么，如何可以写一个EntityFramwork code，这将有同样的结果，因为这

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

我不知道如何将它转换在EF，所以我试图与加入。

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
}

但结果是不一样的，递归的CTE

But the result is not same, 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
                            ");

这是我怎么做的，现在使用EF。

This is how I am doing now using EF.

推荐答案

EF不支持递归CTE的。使用视图或表值函数。

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

这篇关于公共表前pression中的EntityFramework的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！