LINQ to SQL用于自引用表？

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.

我有一个自引用类别表。每个类别都有一个CategoryID，ParentCategoryID，CategoryName等。每个类别可以有任意数量的子类别，每个子类别可以包含任意数量的子类别，依此类推。所以基本上树可以是X级深。

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?

然后，产品与叶（子）类别相关联。有没有办法使用LINQ to SQL获取任何给定类别的所有产品（这将是与其所有叶子后代关联的所有产品）？

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

这感觉就像一个递归问题。是否更好地使用存储过程？

4 个解决方案

#1

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.

我不认为linq-to-sql对这个问题有很好的答案。由于您使用的是sql server 2005，因此可以使用CTE进行分层查询。存储过程或内联查询（使用DataContext.ExecuteQuery）都可以解决问题。

#2

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

好吧，这是一个使用LINQ的可怕匆忙实现。不要用这个:-)

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

#3

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).

性能方法是创建一个插入/修改/删除触发器，该触发器维护一个完全不同的表，该表包含所有节点的所有祖先的节点 - 祖先对。这样，查找是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.

要使用它来获取属于节点及其所有后代的所有产品，您只需选择将目标节点作为祖先的所有类别节点。在此之后，您只需选择属于任何这些类别的任何产品。

#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.

我处理这个的方法是使用一些扩展方法（过滤器）。我已经从我实现过的项目中编写了一些示例代码。请特别注意我正在填充ParentPartner对象和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;
        }