LINQ父子关系 [英] LINQ parent child relation

问题描述

我工作在MVC的BLOG功能。我需要能够创建博客评论。因此，每个评论可能有一个家长意见等。

I'm working on the BLOG functionality in MVC. I need to be able to create 'blog comments'. So each comment may have a parent comment etc.

由于表点评： CommentId - > INT - >身份自动增量 PostId - > INT 的ParentId - > INT 注释 - >字符串

Given table "Comments": CommentId -> int -> identity autoincrement PostId -> int ParentId -> int Comment -> string

有没有办法得到的评论列表，对于给定的物品订购CREATEDATE和的ParentId？

Is there a way to get a list of comments for a given article ordered by CreateDate and ParentId?

或者，也许有更好的表的设计，你可以建议。什么是插入这样的帖子的评论时，最好的设计？

Or maybe there is a better table design you may suggest. What is the best design when inserting Post comments like this?

我使用实体框架。

感谢

推荐答案

我想我明白你在做什么。让我看看，如果我第一次知道了。

I think I understand what you're trying to do. Let me see if I've got it first.

由于所选择的 PostID 要返回，使顶层的意见（即无父）在创造顺序返回所有的意见，和所有的子评论在返回每个父母后，下一个不相关的父评论之前创建顺序。是吗？

Given a selected PostID you want to return all comments so that top-level comments (ie no parent) are returned in creation order, and all child comments are returned in creation order after each parent and before the next unrelated parent comment. Is that right?

我创建了下面的类和测试数据：

I created the following class and test data:

public class Comment
{
    public int CommentId { get; set; }
    public int PostId { get; set; }
    public int? ParentId { get; set; }
    public string Content { get; set; }
}

var comments = new List<Comment>()
{
    new Comment() { PostId = 2, CommentId = 1, },
    new Comment() { PostId = 2, CommentId = 2, ParentId = 1, },
    new Comment() { PostId = 2, CommentId = 3, },
    new Comment() { PostId = 2, CommentId = 4, ParentId = 1, },
    new Comment() { PostId = 2, CommentId = 6, ParentId = 5, },
    new Comment() { PostId = 2, CommentId = 7, ParentId = 1, },
    new Comment() { PostId = 2, CommentId = 8, ParentId = 3, },
    // PostId = 3 to test the filter is working
    new Comment() { PostId = 3, CommentId = 9, },
    // Move this last to test the ordering is working
    new Comment() { PostId = 2, CommentId = 5, ParentId = 3, },
};

我假定 CommentId 将自动递增，以便它可以被用来确定创建顺序

I assumed that the CommentId will be auto-incrementing so that it can be used to determine the creation order.

所以，如果我理解你的要求，你希望下面的输出顺序：

So if I understand your requirement you want the following output order:

id == 1 [pid == ]
id == 2 [pid == 1]
id == 4 [pid == 1]
id == 7 [pid == 1]
id == 3 [pid == ]
id == 5 [pid == 3]
id == 6 [pid == 5]
id == 8 [pid == 3]

这是需要执行的查询是这样的：

The query that needs to execute is this:

var postId = 2;

var childCommentsLookup =
    (from c in comments
     where c.PostId == postId
     orderby c.CommentId
     select c).ToLookup(x => x.ParentId);

该查询没有订购的意见，但不会强制一个查询数据库中执行并返回所有与其相关的评论 postId 。

This query doesn't order the comments, but does force a single query to execute in the database and returns all the comments associated with the postId.

现在这里有两种方法来获取意见以正确的顺序。

Now here are two ways to get the comments in the right order.

首先，递归的lambda EX pression：

Firstly, a recursive lambda expression:

//Must declare this as null to allow recursive calling of the lambda.
Action<int?, ILookup<int?, Comment>> output = null;

output = (p, l) =>
{
    foreach (var c in l[p])
    {
        Console.WriteLine("id == {0} [pid == {1}]", c.CommentId, c.ParentId);
        output(c.CommentId, l);
    }
};

output(null, childCommentsLookup);

其次，你可以使用一个迭代的方法来简单地得到一个的IEnumerable＆LT;注释＆GT; 以正确的顺序评论：

Secondly, you could use an iterator method to simply get an IEnumerable<Comment> with the comments in the right order:

public static IEnumerable<Comment> OrderCommentsRecursively(
    int? parent, ILookup<int?, Comment> lookup)
{
    foreach (var c0 in lookup[parent])
    {
        yield return c0;
        foreach (var c1 in OrderCommentsRecursively(c0.CommentId, lookup))
        {
            yield return c1;
        }
    }
}

foreach (var c in OrderCommentsRecursively(null, childCommentsLookup))
{
    Console.WriteLine("id == {0} [pid == {1}]", c.CommentId, c.ParentId);
}

现在，如果你要创建一个迭代函数我多走一步的做一个很好的方法，直接返回结果：

Now, if you're going to create an iterator function I'd go one step further an make a nice method to return your results directly:

public static IEnumerable<Comment> GetRecursivelyOrderedCommentsByPostId(
    IEnumerable<Comment> comments, int postId)
{
    return OrderCommentsRecursively(null,
        (from c in comments
         where c.PostId == postId
         select c).ToLookup(x => x.ParentId));
}

foreach (var c in GetRecursivelyOrderedCommentsByPostId(comments, postId))
{
    Console.WriteLine("id == {0} [pid == {1}]", c.CommentId, c.ParentId);
}

这两个/三种方法的结果是：

The results from these two/three approaches are:

Lambda Expression:
id == 1 [pid == ]
id == 2 [pid == 1]
id == 4 [pid == 1]
id == 7 [pid == 1]
id == 3 [pid == ]
id == 5 [pid == 3]
id == 6 [pid == 5]
id == 8 [pid == 3]

Iterator Call:
id == 1 [pid == ]
id == 2 [pid == 1]
id == 4 [pid == 1]
id == 7 [pid == 1]
id == 3 [pid == ]
id == 5 [pid == 3]
id == 6 [pid == 5]
id == 8 [pid == 3]

Query & Iterator Call:
id == 1 [pid == ]
id == 2 [pid == 1]
id == 7 [pid == 1]
id == 4 [pid == 1]
id == 3 [pid == ]
id == 5 [pid == 3]
id == 6 [pid == 5]
id == 8 [pid == 3]

我希望这有助于。

I hope this helps.

这篇关于LINQ父子关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！