本文介绍了Linq-to-Sql:递归获取子级的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个Comment表,其中包含CommentID和ParentCommentID.我正在尝试获取评论的所有子级的列表.到目前为止,这是我到目前为止尚未完成的测试.

I have a Comment table which has a CommentID and a ParentCommentID. I am trying to get a list of all children of the Comment. This is what I have so far, I haven't tested it yet.

private List<int> searchedCommentIDs = new List<int>();
// searchedCommentIDs is a list of already yielded comments stored
// so that malformed data does not result in an infinite loop.
public IEnumerable<Comment> GetReplies(int commentID) {
    var db = new DataClassesDataContext();
    var replies = db.Comments
        .Where(c => c.ParentCommentID == commentID
            && !searchedCommentIDs.Contains(commentID));
    foreach (Comment reply in replies) {
        searchedCommentIDs.Add(CommentID);
        yield return reply;
        // yield return GetReplies(reply.CommentID)); // type mis-match.
        foreach (Comment replyReply in GetReplies(reply.CommentID)) {
            yield return replyReply;
        }
    }
}

2个问题:

  1. 有什么明显的方法可以改善这一点吗? (此外,也许可以在SQL中使用CTE创建视图.)
  2. 为什么我不能只给Comment产生IEnumerable <Comment>到IEnumerable <Comment>?
  3. 在这种情况下是否仍然可以使用SelectMany?
  1. Is there any obvious way to improve this? (Besides maybe creating a view in sql with a CTE.)
  2. How come I can't yield a IEnumerable <Comment> to an IEnumerable <Comment>, only Comment itself?
  3. Is there anyway to use SelectMany in this situation?

推荐答案

我可能会使用UDF/CTE,或者使用(对于非常深的结构而言)手动执行相同操作的存储过程.

I'd probably use either a UDF/CTE, or (for very deep structures) a stored procedure that does the same manually.

请注意,如果您可以更改架构,则可以将此类递归结构预索引到索引树/范围树中,从而可以执行单个BETWEEN查询-但树的维护成本很高(即查询变得便宜,但是插入/更新/删除变得昂贵,或者您需要延迟的计划任务.

Note that if you can change the schema, you can pre-index such recursive structures into an indexed/ranged tree that lets you do a single BETWEEN query - but the maintenance of the tree is expensive (i.e. query becomes cheap, but insert/update/delete become expensive, or you need a delayed scheduled task).

Re 2-您只能yield枚举中指定的类型(IEnumerable<T>/IEnumerator<T>中的T).

Re 2 - you can only yield the type specified in the enumeration (the T in IEnumerable<T> / IEnumerator<T>).

如果方法返回IEnumerable<IEnumerable<Comment>>,您可以yield一个IEnumerable<Comment> -这样做有意义吗?

You could yield an IEnumerable<Comment> if the method returned IEnumerable<IEnumerable<Comment>> - does that make sense?

改进:

  • 也许是使用CTE递归方法的 udf (以保持可组合性,而不是存储过程)
  • 使用using,因为DataContextIDisposable ...
  • perhaps a udf (to keep composability, rather than a stored procedure) that uses the CTE recursion approach
  • use using, since DataContext is IDisposable...

如此:

using(var db = new MyDataContext() ) { /* existing code */ }

  • LoadWith 值得一试,但我不值得我一定会充满希望...
  • 搜索到的ID列表作为一个字段是有风险的-只要您不两次调用它,我想您就可以了...就我个人而言,我会在私有支持方法上使用一个参数...(即在递归调用之间传递列表,但不在公共API上传递
    • LoadWith is worth a try, but I'm not sure I'd be hopeful...
    • the list of searched ids is risky as a field - I guess you're OK as long as you don't call it twice... personally, I'd use an argument on a private backing method... (i.e. pass the list between recursive calls, but not on the public API)
    • 这篇关于Linq-to-Sql:递归获取子级的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-18 21:58