Linq to SQL: Select categories that have no subcategories

Question

I need to grab all categories that don't have subcategories

1
^--1.1
^--1.2
    ^--1.2.3

2
^--2.1

3

In this example I would want to get [1.1], [1.2.3], [2.1] and [3].

My Categories table looks like this:

CategoryID | CategoryName | ParentID

I figure I should be selecting all the categories where it's CategoryID isn't used in any other category's ParentID field but I'm at a loss as to how to write that.

Answer 1

Try this:

from c in model.Categories
where !(from inner_c in model.Categories
        select inner_c.ParentID).Contains(c.CategoryID)
select c

The subquery is key - we're trying to translate into LINQ:

SELECT * FROM categories WHERE categoryID NOT IN (SELECT parentID FROM categories)

Answer 2

Try:

List<Category> GetChildlessCategories()
{
    List<int> parentIds = context.Categories.Select(x => x.ParentId);
    return context.Categories.Where(c => !parentsIds.Contains(c.CategoryId));
}

It is easier to work on the flat table that to load it to a relational structure and try to process each node again the whole (flat) structure again.