LINQ to determine if children exist

Question

For simplicity, a "Section" object contains the following properties:

SectionId
ParentSectionId
Name

I currently have the following LINQ code to obtain child sections of a given section:

List<Section> sections = SectionCache.GetAllSections();

sections.AsQueryable().Where(s => s.ParentSectionId == 10);

This gives me all children of the section with a SectionId of 10 (again, for simplicity), but I need to develop this further to only include sections that themselves have children. In SQL, I might do something like this:

SELECT      Section.SectionId,
            Section.ParentSectionId,
            Section.Name
FROM        Section
INNER JOIN  Section children ON children.ParentSectionId = Section.SectionId
WHERE       Section.ParentSectionId = 10
GROUP BY    Section.SectionId,
            Section.ParentSectionId,
            Section.Name
HAVING      COUNT(children.SectionId) > 0

How can I achieve this in LINQ/what is the best way to achieve this with LINQ?

Thanks

Answer 1

I prefer the SQL-like LINQ syntax. It's more readable then the fluent one. :-)

        var sections = SectionCache.GetAllSections().AsQueryable();
        var filteredSections = from s in sections
                               let cc = (from c in sections
                                          where (c.ParentSectionId == s.SectionId)
                                          select c).Count()
                               where (s.ParentSectionId == 10) && (cc > 0)
                               select s;

Answer 2

Here's a couple ways of doing it (these are pretty much identical)

IEnumerable<Section> query =
  sections.Where(s =>
    sections.Any(c => s.SectionId = c.ParentSectionId))
  );

IEnumerable<Section> query =
  from s in sections
  where (
   from c in sections
   where c.ParentSectionId == s.SectionId)
   select c).Any()
  select s;

Or more optimally:

ILookup<int, Section> childLookup =
  sections.ToLookup(c => c.ParentSectionId);
IEnumerable<Section> query =
  sections.Where(s => childLookup[s.SectionId].Any());

Then there's the groupjoin technique, which should be pretty efficient too:

IEnumerable<Section> query =
  from s in sections
  join c in sections
    on s.SectionId equals c.ParentSectionId
    into children
  where children.Any()
  select s;

Answer 3

Here are a couple if ideas for you, if I'm reading your requirements right

// one method
var query = from section in sections
            where sections.Any(s => s.ParentSectionId == section.SectionId)
            select section;

// another method 
var query2 = (from section in sections
                join child in sections
                on section.SectionId equals child.ParentSectionId
                select section).Distinct();

With a list populated in this manner

List<Section> sections = new List<Section>()
{
    new Section () { SectionId = 1, ParentSectionId = 0, Name = "Alpha" },
    new Section () { SectionId = 2, ParentSectionId = 0, Name = "Bravo" },
    new Section () { SectionId = 3, ParentSectionId = 0, Name = "Charlie" },
    new Section () { SectionId = 4, ParentSectionId = 1, Name = "Apple" },
    new Section () { SectionId = 5, ParentSectionId = 2, Name = "Banana" },
    new Section () { SectionId = 6, ParentSectionId = 4, Name = "Aardvark" },
    new Section () { SectionId = 7, ParentSectionId = 4, Name = "Antelope" }
};

The queries would return Alpha, Bravo, and Apple.

Answer 4

        List<Section> sections = new List<Section>();
        sections.Add(new Section { SectionId = 1, ParentSectionId = 0, Name ="S1" });
        sections.Add(new Section { SectionId = 2, ParentSectionId = 1, Name = "S2" });
        sections.Add(new Section { SectionId = 3, ParentSectionId = 1, Name ="S3" });
        sections.Add(new Section { SectionId = 4, ParentSectionId = 2, Name ="S4" });
        sections.Add(new Section { SectionId = 5, ParentSectionId = 2, Name ="S5" });

        var result = sections.GroupJoin(sections, p => p.SectionId, chld => chld.ParentSectionId, (p, chld) => new { Parent = p, Children = chld })
                           .Where(g => g.Children.Any())
                           .Select(g => g.Parent);