Geek Answers Handbook

Use Linq to query for terminal / sheet nodes in a hierarchical table / composite template

I have a self-binding table with id, CategoryName and ParentId. This is a typical scenario of a hierarchical table of categories that can be divided into categories that DB experts tell me is called an adjacency model.

I want to use Linq to SQL to query subcategories that are themselves not related to any other subcategories, that is, they are direct leaf nodes of any particular category or subcategory.

The simple part I got is that it just gets subcategories. Almost embarrassed to put the code here. But we really like the code.

IList<Categories> subcategories = context.Where( c => c.ParentId == 1).ToList();

But narrowing it down to categories without subcategories turns me on. Any help would be greatly appreciated.

Thanks for the help. Jeff

UPDATE ** It would seem that this works, but if someone can confirm that it is "correct", I would be grateful. So, if I want leaf nodes under a category with Id = 1, I would do the following:

Categories.Where( c => !c.Children.Any ( d => d.ParentId == c.Id)).Where( e => e.ParentId == 1)

"Children" is the Linq name giving an association of self-regulation.

+3
source share
2 answers

Your solution is correct because the method Any()translates to sql "EXISTS ()" and !c.Children.Any ( d => d.ParentId == c.Id))translates to a sql clause likeNOT EXISTS (SELECT * FROM Categories WHERE ParentID = outerRef.ID)

Another way to do this is to use Count:

Categories.Where( c => c.Children.Count(d => d.ParentId == c.Id) == 0).Where( e => e.ParentId == 1)

EXISTS() COUNT() sql ( ), Any() .

+2

, , , ... , , node , .

, , ...

   (from c in context
    join cc in context on c.id equals cc.parentid into temp
    from t in temp.DefaultIfEmpty()
    where t == null
    select c).ToList()
0

More articles:


All Articles