LINQ to SQL- Check if object has selected child, grandchild etc

Question

I've been looking for an answer everywhere, but can't find anything. I have two tables, Media and Keywords, which have a many to many relationship. Now the Keywords table is quite simple - it has a ID, Name and ParentFK column that relates to ID column (it's a tree structure). The user can assign any single keyword to the media file, which means that he can select a leaf without selecting the root or branch.

Now I have to be able to determine if a root keyword has any child, grandchild etc. which is assigned to a media object, but I have to do it from the root.

Any help will be appreciated.

possible duplicate of [How to optimize LINQ-to-SQL for recursive queries?](http://stackoverflow.com/questions/6590942/how-to-optimize-linq-to-sql-for-recursive-queries). This subject is a recurring topic :) here at StackOverflow. — Gert Arnold, Apr 02 '13 at 11:31
A recursive function using Any linq operator can suggest you a solution? Take a look http://msdn.microsoft.com/library/system.linq.enumerable.any.aspx For further help please post some code. — michele, Apr 02 '13 at 11:31

Jan P. · Accepted Answer · 2013-04-02T11:47:19.020

1

Just look for any entry, which has the given ParentFK set with your ID.

public static bool HasChild(int id) {
    return
        db.Keywords.Any(item => item.Parent == id);
}

public static bool HasGrandChilds(int id) {
    return
        db.Keywords.Where(item => item.Parent == id).Any(item => HasChild(item.ID);
}

A more generic way:

public static bool HasGrandChilds(int id, int depth) {
    var lst = new List<Keywords>();
    for (var i = 0; i < depth - 1; i++) {
        if (i == 0)
        {
            //Initial search at first loop run
            lst = db.Keywords.Where(item => item.ParentId == id);
        }
        else
        {
            //Search all entries, where the parent is in our given possible parents
            lst = db.Keywords.Where(item => lst.Any(k => k.Id == item.Parent));
        }
        if (!lst.Any())
        {
            //If no more children where found, the searched depth doesn't exist
            return false;
        }
    }
    return true;
}

edited Apr 02 '13 at 11:47

answered Apr 02 '13 at 11:33

Jan P.

3,261
19
26

That works fine with child elements, but doesn't solve the problem with grandchildren :) – Szymon Sajdak Apr 02 '13 at 11:35
The problem is I don't know how deep will this go :( – Szymon Sajdak Apr 02 '13 at 11:40
Added a more generic way, for variable depth – Jan P. Apr 02 '13 at 11:47
After tweaking this code to my needs, everything works fine :D Thanks! – Szymon Sajdak Apr 02 '13 at 12:50

score 1 · Answer 2 · answered Apr 02 '13 at 11:39

From your current schema I can't think of a better solution than the following:

Issue a query to retrieve a list of all children of the root.
Issue queries to retrieve a list of all children of the children from the previous step.
So on, recursively to create a list of all descendants of the root.
Next query the DB for all media objects that have any of the keywords in the list.

But the above algorithm will entail multiple calls to the DB. You can make it in a single query of you refine your schema a little. I would suggest that you keep for each keyword not only its parent FK, but also its root FK. This way you could issue a single query to get all objects that have a keyword whose root FK is the desired one.

LINQ to SQL- Check if object has selected child, grandchild etc

2 Answers2