将包含'with'cte的sql语句转换为linq [英] Converting sql statement that contains 'with' cte to linq

问题描述

我在这里有这段代码，已经和它战斗了几个小时.基本上，此sql语句所做的是获取指定文件夹(@compositeId)的 ALL 子文件夹.

I have this piece of code here, been battling with it for hours. basically what this sql statement does is gets ALL subfolders of a specified folder (@compositeId).

WITH auto_table (id, Name, ParentID) AS
(
SELECT
    C.ID, C.Name, C.ParentID
FROM Composite_Table AS C
    WHERE C.ID = @compositeId

UNION ALL

SELECT
    C.ID, C.Name, C.ParentID
FROM Composite_Table AS C
    INNER JOIN auto_table AS a_t ON C.ParentID = a_t.ID
)

SELECT * FROM auto_table

此查询将返回如下内容:

This query would return something like this:

Id   |    Name    | ParentId
1    | StartFolder| NULL
2    | Folder2    | 1
4    | Folder3    | 1
5    | Folder4    | 4

现在，我想将代码转换为linq.我知道它涉及某种形式的递归，但是由于with语句的存在，它仍然停滞不前.

Now I want to convert the code to linq. I know it involves some form of recursion but still stuck thanks to the with statement.

推荐答案

没有与SQL等效的Linq可以(有效地)做到这一点.最好的解决方案是从Linq调用包含该语句的SP/View/UDF.

There is no Linq to SQL equivalent that can do that (in an efficient manner). Best solution would be to call a SP/View/UDF from Linq containing that statement.

这篇关于将包含'with'cte的sql语句转换为linq的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！