Linq query to select nested children

Question

I have a multi-hierarchical class structure. That is a 'topic' can have many children and a 'topic' can belong to more than one parent. Here is my class structure:

public abstract class Hierarchy <T>{
 public virtual ICollection<T> Parents { get; set; }
 public virtual ICollection<T> Children{ get; set; }
}

public class Topic: Hierarchy <T>
{
  public long ID {get;set;}
  public string Title{get;set;}
}

Now for each topic along with Id, Title, I want to select all child Ids (should include nested children as well).

This is my query:

var result = from x in db.topics
             select new TopicsDO{
             Id = x.Id,
             Title = x.Title,
             ChildIds = x.Children.SelectMany(x=>x.Id) //This does not give nested child Ids, it just returns the immediate child Ids.
             }

Thanks for help.

Possible duplicate of [Writing Recursive CTE using Entity Framework Fluent syntax or Inline syntax](https://stackoverflow.com/questions/11929535/writing-recursive-cte-using-entity-framework-fluent-syntax-or-inline-syntax) — Antonín Lejsek, Oct 28 '18 at 05:32

Joseph Wu · Answer 1 · 2018-10-28T05:24:08.127

0

Are you using EntityFramework? then you can use higher level functions provided by DbContext to achieve what you try to accomplish, for example:

using (var context = new TopicContext())
{
  var topicsDO = context.Topics
    .Include(t=> t.Childrens)
    .Select(a => new
    {
        Id = a.BlogId,
        Title= a.Url,
        ChildrenIds = a.Children.Select(x => x.Id).ToArray()
    })
    .ToList();
}

if you are determined to use sql, you can use Join table Children to achieve what you what.

var result = from x in db.topics
            Join y in db.childrens On x.Id equals y.topicId Into childrenGroup
         select new TopicsDO{
         Id = x.Id,
         Title = x.Title,
         ChildrenGrp = childrenGroup
         }

edited Oct 28 '18 at 05:24

answered Oct 28 '18 at 04:59

Joseph Wu

4,786
1
21
19

Hi @Joe , thanks for help. I am using entity framework and the solution you gave returns immediate children Id's. I need Id of all children available in hierarchy. – user3014311 Oct 28 '18 at 11:49
I see. I didn't read the problem correctly. I think you can try to use context.ExecuteQuery to execute sql queries. In the query, try to use Cross Apply – Joseph Wu Oct 28 '18 at 12:14

Linq query to select nested children

1 Answers1