CODEWITHSUNDEEP
c#sqllinqentity-framework
Frank
Frank

Reputation: 4119

Linq query which retrieved parent/child of children with a collection

I have a self referencing Category class from which I would like to retrieve parent categories and all corresponding children if it has at least one child category and has at least 1 or more activities (ICollection<Activity>) in the collection.

This would also go for children of children as these should only be returned if there are children categories with at least 1 or more activities.

If there are no child categories with at least 1 or more activities the parent or child Category should not be returned.

The query should return the parent Category as an actual Category object and not just the CategoryId. It this possible?

public class Category
{
    public int CategoryId { get; set; }
    public string Name { get; set; }
    public int? ParentId { get; set; }
    public virtual Category Parent { get; set; }

    public virtual ICollection<Category> Children { get; set; }
    public virtual ICollection<Activity> Activities { get; set; }
}

UPDATE 1

The query which partially works:

 var categories = _db.Categories
        .Where(x => x.Parent != null &&  x.Activities.Count > 0)
        .GroupBy(x => x.ParentId)
        .Select(g => new { Parent = g.Key, Children = g.ToList() }).ToList();

Upvotes: 1

Views: 2765

Answers (1)

Zachary Kniebel
Zachary Kniebel

Reputation: 4794

Let's start off a bit smaller, since the query you are looking to create is somewhat complex. We will create your query from the bottom up. First off, you want to eliminate categories that do not have any child categories with at least one or more activities. Let's make a Predicate to return true for those that should be included and false for those that should be excluded, at a single level. We will do this in two stages. First, let's make a predicate that returns true for categories that have activities:

Predicate<Category> hasActivities = cat => cat.Activities.Any();

Second, let's make a Predicate to return true for those categories with child categories that have activities:

Predicate<Category> hasChildWithActivities = 
    parentCat => parentCat.Children.Any(hasActivities);


Now let's create the filter query that will filter a given Category's descendants. To do this, we will create a Func that takes a parent Category, performs the logic and returns the updated Category:

Func<Category, Category> getFilteredCategory =
    parentCat => 
    {
        parentCat.Children = parentCat.Children
            .Where(hasChildWithActivities)
            .Select(getFilteredCategory);

        return parentCat;
    });

Note that this is equivalent to:

Func<Category, Category> getFilteredCategory = delegate(Category parentCat)
{
    parentCat.Children = parentCat.Children
        .Where(hasChildWithActivities)
        .Select(getFilteredCategory);

    return parentCat;
};


In your OP, you mentioned that you wanted to filter parents as well. You can use this same logic on the parents by traversing up to the top level and running this query, or by creating a separate query with "joins" or more complex "select" statements. IMHO, the latter would likely be messy and I would advise against it. If you need to apply the logic to parents as well, then first traverse up the tree. Either way, this should give you a good start.


Let me know if you have any questions. Good luck and happy coding! :)

Upvotes: 1

Related Questions