Understanding Recursive LINQ function

Question

I have the following table

ItemNumber | FirstId | SecondId | ClientId
1          | 14      | 16       | NULL
2          | 17      | 18       | 1233242323
3          | 14      | 18       | 1233242323
5          | 15      | 12       | NULL
6          | 14      | 8        | 324234252
7          | 19      | 14       | 324234252
8          | 18      | 19       | 324234252
9          | 20      | 18       | 324234252

With following Class

public class ClientObject
{
    public int ItemNumber { get; set; }
    public int FirstId { get; set; }
    public int SecondId { get; set; }
    public double ClientId { get; set; }

    public ClientObject()
    {

    }
}

Given the FirstId and ClientId, I want to return SecondId where no matching is found in the FirstId column. Eg: Starting with FirstId as 20 ItemNumber 9), I would like to return 8 (ItemNumber 6).

I am trying to use a recursive function approach but not sure if it is right or if there is a better way to approach this problem.

public ClientObject GetItemRecursive(int initialId, double client)
{
    var returnThis = databaseCtxt.TableToUse
        .Where(x => x.FirstId == initialId && x.ClientId == client)
        .AsEnumerable() // updated from suggestion 
        .Select(x => GetItemRecursive(x.SecondId, x.ClientId))
        .FirstOrDefault();
    return returnThis ;
}

I have tried to set this up locally but have not been able to as this is a very very tiny part of large project, so I created the smallest examples here.

That's not going to work with EF as it will not be able to translate it to SQL. You could throw a `AsEnumerable` before the `Select`. — juharr, May 07 '20 at 20:54
@juharr, I made that update. Could you please help me understand how the recursion will work in this case. (after adding the AsEnumerable) I can update the question. — Maddy, May 07 '20 at 20:55
If you are using SQL Server, better approach would be to try to use [recursive queries with CTE's](https://stackoverflow.com/questions/14518090/recursive-query-in-sql-server) — Guru Stron, May 07 '20 at 20:55
Actually that's going to just keep going until there's no match and you'll end up with a `null`. I think you need to do the initial query to get initial match, then pass it's secondid to the recursive call. Then return the initial if the recursive call returns null, otherwise return the result of the recursive call. But a recursive SQL query with a CTE would be more performant. — juharr, May 07 '20 at 21:02

juharr · Accepted Answer · 2020-05-08T12:10:01.670

You actually need to check if the recursive call returns a null meaning there's no match and you've got to the "leaf".

public ClientObject GetItemRecursive(int initialId, double client)
{
    var initial = databaseCtxt.TableToUse
        .FirstOrDefault(x => x.FirstId == initialId && x.ClientId == client);
    if(initial == null) return null; 
    var child = GetItemRecursive(initial.SecondId, client);
    return child ?? initial;
}

Or you could write it without recursion

public ClientObject GetItemRecursive(int initialId, double client)
{
    ClientObject parent = null;
    bool childFound = true;
    while(childFound)
    {
        var id = parent?.SecondId ?? initialId
        var child = databaseCtxt.TableToUse
            .FirstOrDefault(x => x.FirstId == id && x.ClientId == client);
        if(child == null) childFound = false;
        else parent = child;
    }

    return parent;
}

Guru Stron · Answer 2 · 2020-05-07T21:10:44.377

I would assume you are looking for something like this

public ClientObject GetItemRecursive(int initialId, double client)
{
    return Inner(initialId, client, null);

    ClientObject Inner(int id, double d, ClientObject acc )
    {
       var current = databaseCtxt.TableToUse
         .Where(x => x.FirstId == id&& x.ClientId == d)
         .FirstOrDefault();
       if(current == null) return acc;    
       return Inner(current.SecondId, current.ClientId, current);
    }
}

This code uses C# 7.0 feature local functions fro convenience.

So general approach is that you make have an accumulator (acc) which is updated on each iteration until you can't find continuation(request to db returns null), when you reach this point you return the accumulator through all nested calls.

Understanding Recursive LINQ function

2 Answers2