linq to sql recursive query

Question

EmployeeId  Name  ManagerId
------------------------------
1           A     null
2           B     null
3           C     1
4           D     3
5           E     2

just using this table, how can a linq query (using linq to sql) be written to fetch the parent data recursively.

For instance if the Employer Id selected is 4 it should give list of employees with Id: 4, 3, 1

Thanks.

Answer 1

This .AsHierarchy() extension method may be useful: link. However, this only works by providing an easy way to throw your results into linked objects. In order to do that, it'll just get all the records and run its own local recursive query.

If you're looking for a LINQ query that will directly translate to a recursive SQL query via LINQ to SQL, you won't find it. For the best performance, a CTE in a stored procedure is probably what you're looking for. If you have a really simple page that needs to load the whole tree anyway, the AsHierarchy method would probably fit your needs.

Answer 2

I'm not sure if this is exactly what you want, but here is one recursive method that uses some linq that makes sure not to enter an infinite loop:

    public static IEnumerable<Employee> GetTreeForEmployeeNumber(this IEnumerable<Employee> source, int startingId) {
        var result = source.Where(x => x.EmployeeId == startingId).FirstOrDefault();
        if (result != null) {
            var resultAsE = new [] { result };
            if (!result.ManagerId.HasValue)
                return resultAsE;
            return resultAsE.Union(source.Except(resultAsE).GetTreeForEmployeeNumber(result.ManagerId.Value));
        }
        return new Employee [] { };
    }

If you have linqpad installed you can test this with the following script:

void Main()
{
    var lst = new [] {
        new Extensions.Employee{ EmployeeId = 1, Name = "A", ManagerId = null }, 
        new Extensions.Employee{ EmployeeId = 2, Name = "B", ManagerId = null }, 
        new Extensions.Employee{ EmployeeId = 3, Name = "C", ManagerId = 1 }, 
        new Extensions.Employee{ EmployeeId = 4, Name = "D", ManagerId = 3 }, 
        new Extensions.Employee{ EmployeeId = 5, Name = "E", ManagerId = 2 }
    };

    lst.GetTreeForEmployeeNumber(4).Dump();
}

public static class Extensions {

    public class Employee {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public int? ManagerId { get; set; }
    }

    public static IEnumerable<Employee> GetTreeForEmployeeNumber(this IEnumerable<Employee> source, int startingId) {
        var result = source.Where(x => x.EmployeeId == startingId).FirstOrDefault();
        if (result != null) {
            var resultAsE = new [] { result };
            if (!result.ManagerId.HasValue)
                return resultAsE;
            return resultAsE.Union(source.Except(resultAsE).GetTreeForEmployeeNumber(result.ManagerId.Value));
        }
        return new Employee [] { };
    }
}