Entity Framework - Reference Search

Question

I am trying to get all referenced values from database until last value is null.

Example: If i have to search ID - 1 from the below table :

╔════╦════════╗
║ ID ║ RefID  ║
╠════╬════════╣
║ 1  ║ 2      ║
║ 1  ║ 3      ║
║ 2  ║ 4      ║
║ 3  ║ Null   ║
║ 4  ║ 6      ║
║ 5  ║ Null   ║
║ 6  ║ Null   ║
╚════╩════════╝

Result : 2 3 4 6

I have tried:

using (DbContext Db = new DbContext())
    {
        string ID = "1";
        List<string> ids = new List<string>();
        while (true)
        {
            List<string> t = Db.Test.Where(x => x.ID == ID).Select(x => x.RefID).ToList();
            foreach (string item in t)
            {
                if (item != null)
                {
                    ID = item;
                    ids.Add(item);
                }
                else
                {
                    break;
                }
            }
        }
    }

Any help is appreciated.

Table looks like Parent/Child table. You can add a new column that store [hierarchy path](https://stackoverflow.com/questions/14241936/how-can-i-generate-a-hierarchy-path-in-sql-that-leads-to-a-given-node) — neer, Sep 15 '17 at 07:24
This sounds like [Get all parents using Entity Framework 6 and LINQ - parent-child relationship](https://stackoverflow.com/questions/46221848/get-all-parents-using-entity-framework-6-and-linq-parent-child-relationship) — Ivan Stoev, Sep 15 '17 at 07:31

Slava Utesinov · Accepted Answer · 2017-09-15T08:01:41.427

private void SearchRec(List<string> ids, List<string> result, DbContext Db)
{    
    var refs = Db.Test.Where(x => ids.Contains(x.ID) && x.RefID != null)
                 .Select(x => RefID).Distinct().ToList();
    var newRefs = refs.Except(result).ToList();        
    if(newRefs.Count > 0)
    {
        result.AddRange(newRefs);
        SearchRec(newRefs, result, Db);
    }           
}

public List<int> Search(string ID)
{
    var result = new List<string>();
    using (var Db = new DbContext())
    {
        SearchRec(new List<string> {ID}, result, Db);
    }
    return result;
}

Usage:

var answer = Search("1");

Entity Framework - Reference Search

1 Answers1