Linq to SQL to return parent rows

Question

I have a table format of

Id (int)
ParentId (int)

I need to query out all rows that are related to each other so ParentId = Id but nested multiple times

I am a bit stuck on how to tackle this one, I may revert to a stored procedure but I really wanted to try this in pure linq to sql.

Answer 1

I think you have 2 options:

• You have to explicitly fetch parents which causes many separate calls to db. I think this kind of architecture (to fetch self-referencing data recursively with one call from db) is not supported in L2S or L2E.

• In these scenarios, I tend to use native capabilities of my db (in this case: CTE). You can use your custom sql queries and also define return types with db.ExecuteQuery<ReturnType>. If you're on sql server 2005+ you can use the following syntax:

  var allGroups =
db.ExecuteQuery(
@"WITH n(Id, ParentId) AS
(SELECT Id, ParentId
FROM MyTable
WHERE Id = {0}
UNION ALL
SELECT nplus1.Id, nplus1.ParentId
FROM MyTable as nplus1, n
WHERE n.ParentId = nplus1.Id)
SELECT Id, ParentId FROM n",
firstChildId).ToList();

This way, you have fetched all entities in one call. Check out How to do an upper recursive self-join in SQL Server? and Simplest way to do a recursive self-join in SQL Server?.