LINQ to SQL for native links?

Question

I have a table of categories of self-regulation. Each category has CategoryID, ParentCategoryID, CategoryName, etc. And each category can have any number of subcategories, and each of these subcategories can have any number of subcategories, etc. Etc. So basically a tree can be X levels.

Products are then associated with leaf categories (sub). Is there a way to get all Products for any given category (which would be all products associated with all its leaf descendants) using LINQ to SQL?

This seems like a recursive problem. Is it better to use a stored procedure?

Answer 1

, linq-to-sql . SQL Server 2005, CTE . , ( DataContext.ExecuteQuery) .

Answer 2

, LINQ. : -)

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

Answer 3

insert/modify/delete, , node -ancestor . , - O (N).

To use it to get all products owned by node and all its descendants, you can simply select all category nodes for which your target node is an ancestor. After that, you simply select any products that belong to any of these categories.

Answer 4

The way I handle this is to use some extension methods (filters). I wrote sample code from a project that I implemented. Look specifically at the lines where I populate the ParentPartner object and the list of subpanels.

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;
        }