LINQ to SQL for self-referencing tables?

Question

Let's see if I can describe this well...

Say, I have a self referencing Categories table. Each Category has a CategoryID, ParentCategoryID, CategoryName, etc. And each category can have any number of sub categories, and each of those sub categories can have any number of sub categories, and so and and so forth. So basically the tree can be X levels deep.

Then Products are associated to leaf (sub) Categories. Is there a way to get all the Products for any given Category (which would be all the products associated to all its leaf descendants) using LINQ to SQL?

This feels like a recursive problem. Is it better to used a Stored Procedure instead?

Thanks in advanced!

Answer 1

Well here is a terrible rushed implementation using LINQ. Don't use this :-)

public IQueryable GetCategories(Category parent)
{
    var cats = (parent.Categories);
    foreach (Category c in cats )
    {
        cats  = cats .Concat(GetCategories(c));
    }
    return a;
}

Answer 2

The performant approach is to create an insert/modify/delete trigger which maintains an entirely different table which contains node-ancestor pairs for all ancestors of all nodes. This way, the lookup is O(N).

To use it for getting all products belonging to a node and all of its descendants, you can just select all category nodes which have your target node as an ancestor. After this, you simply select any products belonging to any of these categories.

Answer 3

I don't think linq-to-sql has a good answer to this problem. Since you are using sql server 2005 you can use CTEs to do hierarchical queries. Either a stored procedure or an inline query (using DataContext.ExecuteQuery) will do the trick.

Answer 4

The way I handle this is by using some extension methods (filters). I've written up some sample code from a project I have implemented this on. Look specifically at the lines where I'm populating a ParentPartner object and a SubPartners List.

public IQueryable<Partner> GetPartners()
     {
      return from p in db.Partners
          select new Partner
          {
           PartnerId = p.PartnerId,
           CompanyName = p.CompanyName,
           Address1 = p.Address1,
           Address2 = p.Address2,
           Website = p.Website,
           City = p.City,
           State = p.State,
           County = p.County,
           Country = p.Country,
           Zip = p.Zip,
                       ParentPartner = GetPartners().WithPartnerId(p.ParentPartnerId).ToList().SingleOrDefault(),
                       SubPartners = GetPartners().WithParentPartnerId(p.PartnerId).ToList()
          };
     }


public static IQueryable<Partner> WithPartnerId(this IQueryable<Partner> qry, int? partnerId)
     {
      return from t in qry
          where t.PartnerId == partnerId
          select t;
     }

public static IQueryable<Partner> WithParentPartnerId(this IQueryable<Partner> qry, int? parentPartnerId)
        {
            return from p in qry
                   where p.ParentPartner.PartnerId == parentPartnerId
                   select p;
        }