How to perform a recursive function in c# using linq

Question

I have the following table:

Column_1          Column_2
val_1              | val_14           
val_2              | val_17           
val_1              | val_2     
val_4              | null  
val_1              | val_3     
val_20             | val_4     
val_17             | null     
val_2              | val_20   
val_14             | val_6
val_14             | null
Val_6              | null
val_3              | val_30
val_3              | val_19

I want to display Column_2 values

Eg: Select with Column_1 = val_1 will return (val_14, val_2, val_3) from Column_2.

Now, I want for each values in (val_14, val_2, val_3) to return also values from Column_2.

In summary:

val_1 => (val_14, val_2, val_3)
val_14 => (val_6, null)
val_6 => null
val_2 => (val_17, val_20)
val_17 => null
val_20 => (val_4)
val_4 => null
val_3 => (val_30, val_19)

etc...

Final output (val_14, val_2, val_3, val_6, val_17, val_20, val_4, val_30, val_19)

I have a function, with string parameter and list of all rows data

public List<string> MyFunction(string value)
{
   return (from s in myListOfData where value.Contains(s.Column_1) select s).ToList();
}

This function return only the first level.

how can i do this query to display all children in linq? My attempts are unsuccessful.

Thank you

similar question https://stackoverflow.com/a/19237897/11954917 — Makwana Prahlad, Nov 25 '20 at 04:44
Questions like this are asked daily. Please look for other questions on LINQ + hierarchy/recursion. — Gert Arnold, Nov 25 '20 at 07:53

score 0 · Answer 1 · edited Jan 09 '21 at 13:42

Assuming this is all in memory and nothing to do with an ORM.

You could use recursion. However, queues and stacks are safer and easier to debug.

Given some weird ill-defined class

public class Data
{
   public int? Col1 { get; set; }
   public int? Col2 { get; set; }
   public Data(int? col1, int? col2)
   {
      Col1 = col1;
      Col2 = col2;
   }
}

You could use an iterator method and a Queue

public static IEnumerable<int> GetRecusive(List<Data> source,int val)
{
   var q = new Queue<int>();
   q.Enqueue(val);

   while (q.Any())
   {
      var current = q.Dequeue();
      var potential = source.Where(x => x.Col1 == current && x.Col2 != null);
      foreach (var item in potential)
      {
         yield return item.Col2.Value;
         q.Enqueue(item.Col2.Value);
      }
   }
}

Usage

// some ill-defined test data
var list = new List<Data>()
{
   new Data(1, 14),
   new Data(2, 17),
   new Data(1, 2),
   new Data(4, null),
   new Data(1, 3),
   new Data(20, 4),
   new Data(17, null),
   new Data(2, 20),
   new Data(14, 6),
   new Data(14, null),
   new Data(6, null),
   new Data(3, 30),
   new Data(3, 19),
};

var results = GetRecusive(list,1);

// compose as a comma separated list 
Console.WriteLine(string.Join(", ",results));

Output

14, 2, 3, 6, 17, 20, 30, 19, 4

Full Demo Here

If you like, you can turn it into an extension method to give you a LINQ Chain Method feel

public static IEnumerable<int> GetRecusive(this List<Data> source, int val)

Important Note : If you have a circular references then kiss your app goodbye. This will be the same for recursion or queues. If you need to protect against this, then I suggest using a HashSet of visited ids

score 0 · Answer 2 · answered Nov 25 '20 at 06:25

Desired order of records is a bit-tricky to get - looks like at first you want plain 1-st level and then traverse tree in down-left direction. It's a bit tricky.
If order is not important you can:

public List<string> MyFunction(string value)
{
  return myListOfData
    .Where(x => value.Contains(x.Column_1) && x.Column2 != null)
    .Select(x => x.Column2)
    .Aggregate(new List<string>(), (t, x) => { 
      t.Add(x);
      t.AddRange(MyFunction(x));
      return t; })
    .ToList();
}

However, this results in lots of intermediate List creation. So better have enumerable:

public IEnumerable<string> MyFunction(string value)
{
  foreach (var record in myListOfData.Where(x => value.Contains(x.Column_1) && x.Column2 != null)
  {
     yield return record.Column_2;
     foreach (var child in MyFunction(record.Column_2))
        yield return child;
  }
}

And then take ToList() of this IEnumerable.
Still, if order is important you need two functions:

public List<string> MyFunction(string value)
{
    .Where(x => value.Contains(x.Column_1) && x.Column2 != null)
    .Select(x => new Tuple<string, IEnumerable<string>>(x.Column2, Traverse(x.Column2))
    .Aggregate(new List<string>(), (t, x) => { 
      t.Add(x.Item1);
      t.AddRange(x.Item2);
      return t; })
    .ToList();
}

public IEnumerable<string> Traverse(string value)
{
  foreach (var record in myListOfData.Where(x => value.Contains(x.Column_1) && x.Column2 != null)
  {
     yield return record.Column_2;
     foreach (var child in MyFunction(record.Column_2))
        yield return child;
  }
}

How to perform a recursive function in c# using linq

2 Answers2