Implementing recursive property loading in EF Core

Question

I'm on .NET Core 1.1.0, EF Core 1.1.0, VS 2015.

I'm writing a system for posts/comments, and I need a function to load a comment and all of its children and their associated properties. Here's a simplified version of my classes:

public class Comment
{
    public long Id { get; set; }

    public string Content { get; set; }

    public User User { get; set; }

    public ICollection<Comment> Replies { get; set; }
}

public class User
{
    public long Id { get; set; }

    public string Name { get; set; }

    public Avatar Avatar { get; set; }
}

public class Avatar
{
    public string Url { get; set; }
}

Any given comment can have any number of replies:

-PARENT
    -CHILD 1
    -CHILD 2
        -CHILD 3
    -CHILD 4
    -CHILD 5
        -CHILD 6
            -CHILD 7

So, given the ID of the parent comment, I need to load the entire tree, including users and their respective avatars. (I have controls elsewhere to make sure these trees don't become unwieldy, I'm not concerned at this point about potentially grabbing way too much data.)

The Loading Related Data page in the EF Core docs is very helpful, but I'm not sure how to best handle this. I've experimented putting some things together but I just can't conceptualize how to fit it all together. To note again: I'm using EF Core 1.1.0, so I do have access to the functions in the "Explicit loading" section.

How can I load the entire tree of comments given a parent comment's ID?

The round-trip to the database for each children might be quite significant. Might be better to do a recursive CTE using SQL and have EF map the results. — ESG, Dec 06 '16 at 04:03
@ESG I've never heard of CTEs before, after reading about them it looks like one might be perfect. I'll take a closer look, thank you! — vaindil, Dec 06 '16 at 17:18

CodingYoshi · Accepted Answer · 2016-12-06T18:43:13.427

I do not have a database so I just did it in memory but if you follow my comments, it will work for you. Notice the objects I have in memory, only comment with id 2 has replies.

LoadComment method is where everything happens. The rest is just setup code I needed.

class Program
{
    static void Main(string[] args)
    {
        var result = LoadComment(1, null);
        Console.ReadKey();

    }



public static Comment LoadComment(long id, Comment com) 
{
   Comment res = new Comment();
   if( com == null ) 
   {
      // You would call your context here and write db.Single(x => x.Id == id).Include(x => x.User.Avatar);
      var first = db.Single( x => x.Id == id );

      res = new Comment { Id = first.Id, Replies = first.Replies.ToList(), User = first.User };
      foreach( var item in first.Replies ) 
      {
         LoadComment( item.Id, item );
      }
   }
   else 
   {
      // You would call your context here and write db.Single(x => x.Id == id).Include(x => x.User.Avatar);
      var child = db.SingleOrDefault( x => x.Id == id );
      if( child == null ) 
      {
         return null;
      }
      com.Replies = new List<Comment>();
      com.Replies.Add( new Comment { Id = child.Id, Replies = child.Replies.ToList(), User = child.User } );
      foreach( var item in child.Replies ) 
      {
         LoadComment( item.Id, com );
      }
   }


   return res;
}

    private static Comment cm1 = new Comment
    {
        Id = 1,
        User = new User { Id = 1, Avatar = new Avatar { Url = "1" } },
        Replies = new List<Comment> {
        new Comment { Id = 2 },
        new Comment { Id = 3 },
        new Comment { Id = 4 },
        new Comment { Id = 5 } },
        Content = "ContentForCommentId1"
    };

    private static Comment cm2 = new Comment
    {
        Id = 2,
        User = new User { Id = 2, Avatar = new Avatar { Url = "2" } },
        Replies = new List<Comment> {
        new Comment { Id = 22 },
        new Comment { Id = 33 },
        new Comment { Id = 44 },
        new Comment { Id = 55 } },
        Content = "ContentForCommentId2"
    };
    private static List<Comment> db = new List<Comment> { cm1, cm2 };

}

This looks very promising! I'm getting this error though in the last `foreach`: `Collection was modified; enumeration operation may not execute.` It happens after loading the first comment's children; the second item in the `foreach` triggers the exception. — vaindil, Dec 06 '16 at 17:12
Sorry there was a small bug in the if and else block. Please retry with the new code in `LoadComment` method. If it does not work, then I would have to create a database and try to test it that way. — CodingYoshi, Dec 06 '16 at 18:44
I ended up using this method, just tweaked a little bit. The updated code you provided works perfectly, I just changed a couple things for my codebase. Thank you so much! — vaindil, Dec 08 '16 at 20:28

score 2 · Answer 2 · answered Jan 14 '19 at 14:42

This is how I solved it. Fairly similar to Yoshi's but could be helpful to someone.

private async Task<Comment> GetComment(Guid id, CancellationToken cancellationToken)
{
    var wm = await _context.Comments
                           .Include(x => x.Replies)
                           .SingleOrDefaultAsync(x => x.Id == id, cancellationToken);

    for (var i = 0; i < wm.Replies.Count; i++)
    {
        if (!wm.Replies[i].IsDeleted)
            wm.Replies[i] = await GetComment(wm.Replies[i].Id, cancellationToken);
    }

    return wm;
}

Elnaz · Answer 3 · 2016-12-06T07:58:02.050

Why you don't use Eagerly Loading? Eagerly loading multiple levels I have used it in my project in .NETCore (VS2015)

It is also possible to eagerly load multiple levels of related entities. The queries below show examples of how to do this for both collection and reference navigation properties.

using (var context = new BloggingContext()) 
{ 
    // Load all blogs, all related posts, and all related comments 
    var blogs1 = context.Blogs 
                       .Include(b => b.Posts.Select(p => p.Comments)) 
                       .ToList(); 

    // Load all users their related profiles, and related avatar 
    var users1 = context.Users 
                        .Include(u => u.Profile.Avatar) 
                        .ToList(); 

    // Load all blogs, all related posts, and all related comments  
    // using a string to specify the relationships 
    var blogs2 = context.Blogs 
                       .Include("Posts.Comments") 
                       .ToList(); 

    // Load all users their related profiles, and related avatar  
    // using a string to specify the relationships 
    var users2 = context.Users 
                        .Include("Profile.Avatar") 
                        .ToList(); 
}

Note that it is not currently possible to filter which related entities are loaded. Include will always bring in all related entities.

this is my reference: "Microsoft.EntityFrameworkCore.SqlServer": "1.1.0-preview2-22683"

I hope it helps you.

From what I understand, I would have to chain `.Include()` and `.ThenInclude()` for every layer I want to bring in, which isn't feasible because the depth of the comments is unknown in advance. Is that incorrect? — vaindil, Dec 06 '16 at 15:43
Ummm..You are right. Exactly you should know the children and Grandchild of course. And also maybe there is some problems with .ThenInclude() for multi-layers. see: http://stackoverflow.com/questions/40953665/how-to-do-multiple-theninclude-navigation-props-after-one-include/40990604#40990604 — Elnaz, Dec 07 '16 at 07:16

Implementing recursive property loading in EF Core

3 Answers3

Linked