转换包含“with”的sql语句cte 到 linq

发布于 2024-11-05 02:00:11 字数 675 浏览 1 评论 0原文

我这里有这段代码，已经与它斗争了几个小时。基本上，此 sql 语句的作用是获取指定文件夹 (@compositeId) 的 ALL 子文件夹。

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

该查询将返回如下内容：

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

现在我想将代码转换为 linq。我知道它涉及某种形式的递归，但由于 with 语句，仍然卡住了。

原文

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

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

北斗星光 2024-11-12 02:00:11

没有 Linq to SQL 等效项可以（以有效的方式）做到这一点。最好的解决方案是从包含该语句的 Linq 调用 SP/View/UDF。

回复收藏 0 原文

岁月苍老的讽刺 2024-11-12 02:00:11

您可以编写代码（无论是否递归）来重复查询数据库，直到获得所有结果。

但我认为没有办法编写一个 LINQ to SQL 查询来一次性获得所需的所有结果，因此最好将查询保留在 SQL 中。

回复收藏 0 原文

情未る 2024-11-12 02:00:11

有一个已知的插件“LinqToDb”，它提供了在 Linq 中获取等效 CTE 的方法

回复收藏 0 原文

流心雨 2024-11-12 02:00:11

public static List<Composite> GetSubCascading(int compositeId)
{
    List<Composite> compositeList = new List<Composite>();

    List<Composite> matches = (from uf in ctx.Composite_Table
    where uf.Id == compositeId
    select new Composite(uf.Id, uf.Name, uf.ParentID)).ToList();

    if (matches.Any())
    {
        compositeList.AddRange(TraverseSubs(matches));
    }

    return compositeList;
}

private static List<Composite> TraverseSubs(List<Composite> resultSet)
{
    List<Composite> compList = new List<Composite>();

    compList.AddRange(resultSet);

    for (int i = 0; i < resultSet.Count; i++)
    {
        //Get all subcompList of each folder
        List<Composite> children = (from uf in ctx.Composite_Table
        where uf.ParentID == resultSet[i].Id
        select new Composite(uf.Id, uf.Name, uf.ParentID)).ToList();

        if (children.Any())
        {
            compList.AddRange(TraverseSubs(children));
        }
    }

    return compList;
}

//All where ctx is your DataContext

public static List<Composite> GetSubCascading(int compositeId)
{
    List<Composite> compositeList = new List<Composite>();

    List<Composite> matches = (from uf in ctx.Composite_Table
    where uf.Id == compositeId
    select new Composite(uf.Id, uf.Name, uf.ParentID)).ToList();

    if (matches.Any())
    {
        compositeList.AddRange(TraverseSubs(matches));
    }

    return compositeList;
}

private static List<Composite> TraverseSubs(List<Composite> resultSet)
{
    List<Composite> compList = new List<Composite>();

    compList.AddRange(resultSet);

    for (int i = 0; i < resultSet.Count; i++)
    {
        //Get all subcompList of each folder
        List<Composite> children = (from uf in ctx.Composite_Table
        where uf.ParentID == resultSet[i].Id
        select new Composite(uf.Id, uf.Name, uf.ParentID)).ToList();

        if (children.Any())
        {
            compList.AddRange(TraverseSubs(children));
        }
    }

    return compList;
}

//All where ctx is your DataContext

回复收藏 0 原文

~没有更多了~