LINQ parent child relation

Question

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.

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

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'm using Entity framework.

thanks

Answer 1

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

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, },
};

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);

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.

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);

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.

Answer 2

Let's suppose you have comments that are 2 levels deep (Most of the websites don't have more than 2 levels and it really isn't needed to have more depth than that).

You can do the following in your view (I am assuming that the view is strongly typed to Post):

<% foreach(var comment in Model.Comments.Where (c=>c.ParentId == null)) { %>
   <%: comment.Text %}
   <% if (Model.Comments.Count(c=>c.ParentId == comment.Id) > 0) {%>
      <% foreach (var child in Model.Comment.Where(c=>c.ParentId == comment.Id)) {%>
         <%: child.Text %>
      <% } %>
   <% } %>
<% } %>

If you want, you can keep adding more levels like this.

Hope this helps.

Answer 3

You might be over-thinking things here.

All comments belong to some blog post. It is doubtful that you would need paging for them so it is pretty safe to get all comments of a post at once and use relationships between comments themselves only for rendering. That is, you would get post.Comments, then render comments.Where(x => x.Parent==null).OrderBy(x => x.CreateDate) recursively with their replies.

Answer 4

If you have a self relation like this you basically have a tree structure with n-levels. You cannot get all in one single query, but you have to make a query for each level.

On way around this is to create a logicalPath column that contains all id's of a rows parents, so that comment id 6 will have (3;1) in that column. The trick is to keep it correct at all times (preferably a trigger). If the most usual is to have only one or two levels, I would keep it simple and create a query for each level