Get recurring child result by depth level

Question

I have "googled" for many hour but still I am not able to find an answer. Basically, I have a table that looks like this:

Parent  |   ID
null    |   1
1       |   2
2       |   3
3       |   4
3       |   5
3       |   6
4       |   7
null    |   8

How can I use entity linq to filter based on Id and "depth level" (which is a simple count of elements including the Id element and n - 1 elements passed the Id element)?

For example when I pass Id 2 and depth level 2

Result will be

Parent  |   ID
2       |   3 //level 1
3       |   4 //level 2
3       |   5 //level 2
3       |   6 //level 2

If I pass Id 3 and depth level also 2

Result will be

Parent     ID
3       |   4 //level 1
3       |   5 //level 1
3       |   6 //level 1
4       |   7 //level 2

Thanks for your help

Could you explain more? the problem is still unclear to me (actually the whole situation is not clear). I may help if you explain more — Mohammed Noureldin, Jun 01 '18 at 03:00
https://stackoverflow.com/questions/11929535/writing-recursive-cte-using-entity-framework-fluent-syntax-or-inline-syntax — Antonín Lejsek, Jun 01 '18 at 03:02
Please check my answer. I am not sure that I understood you correctly. If not, please tell me to remove the answer. — Mohammed Noureldin, Jun 01 '18 at 03:11

score 0 · Answer 1 · answered Jun 01 '18 at 03:22

I tired to simulate your scenario in a Console application. Following is one of the possible solution. Its very basic so you need to change and use it as you need. But it returns results as per logic you mention in your question.

class Program
{
    static List<Data> data = new List<Data>();
    static List<Data> result = new List<Data>();

    static void Main(string[] args)
    {

        data.Add(new Data() { Parent = null, ID = 1 });
        data.Add(new Data() { Parent = 1, ID = 2 });
        data.Add(new Data() { Parent = 2, ID = 3 });
        data.Add(new Data() { Parent = 3, ID = 4 });
        data.Add(new Data() { Parent = 4, ID = 5 });
        data.Add(new Data() { Parent = null, ID = 6 });

        // Take() implementation is for Depth. 
        result = findChildren(3).Take(2).ToList();

        Console.ReadLine();
    }

    static List<Data> findChildren(int Id)
    {
        return data.Where(x => x.Parent == Id ).Union(data.Where(x => x.Parent == Id).SelectMany(y => findChildren(y.ID))).ToList();
    }

}

public class Data
{
    public int? Parent { get; set; }
    public int ID { get; set; }
}

If he uses EF, he needs translation to sql. This approach would work for linq to objects only. — Antonín Lejsek, Jun 01 '18 at 03:56
It will work with EF as well. He needs to use context and table in stead of List. — Gaurang Dave, Jun 01 '18 at 06:08
hi thanks for your answer. but I tink take(2) means only take the two record only, am I right? can you check my latest scenario ? — user998405, Jun 01 '18 at 09:12
You changed to this after I post my answer. I need to check again in details. StackOverflow good practice is not to change question with major update. I will get back to you if possible. — Gaurang Dave, Jun 01 '18 at 09:17

Slava Utesinov · Answer 2 · 2018-06-01T06:05:44.510

0

var Id = 2;
var Level = 2;

var head = new Item { Id = Id };
var result = new List<Item>();
for(var i = 0; i < Level; i++)
{
    head = context.Table.FirstOrDefault(x => x.ParetId == head.Id);
    if(head != null)
        result.Add(head);
    else
        break;
}

edited Jun 01 '18 at 06:05

answered Jun 01 '18 at 06:00

Slava Utesinov

13,410
2
19
26

Get recurring child result by depth level

2 Answers2