Solution for category tree with less query?

Question

Basically, the Model is like this:

----------------------------------
| CAT_ID | CAT_NAME | CAT_PARENT |
----------------------------------

which make this a recursive relationship. But how should we use this model in our Asp.net mvc web application with least amount of query. My temporary solution is pass the object in to the function and let the function pass the object, instead of requerry, but this is the code i tried:

public string CategoryTree(this HtmlHelper html, IEnumerable<Category> categories, int? parent)
{
    parent = parent ?? 0;
    string htmlOutput = string.Empty;
    var cat = categories.Where(d => d.CAT_PARRENT == parent.Value);
    if (cat.Count() > 0)
    {
        htmlOutput += "<ul>";
        foreach (Category category in cat)
        {
            htmlOutput += "<li>";
            htmlOutput += category.CAT_NAME;
            htmlOutput += html.CategoryTree(categories, category.CAT_ID);
            htmlOutput += "</li>";
        }
        htmlOutput += "</ul>";
    }

    return htmlOutput;
}

but this generate like 4 query for 4 row of category. So this is not a good solution for this problem. Im using linq to sql.

Answer 1

Don't forget that .Count() may also generate a query; you can use Where().ToList() and then its .Count since you're going to use the full collection anyway.

Alternative approach would be to collect CAT_IDs first, get all related children for all of them, and then pass children as a collection hint to your method. Smth like

public string CategoryTree(this HtmlHelper html, IEnumerable<Category> categories, IList<Category> cats)
{
      var query = categories.Where(x => false);
      // a query to select ALL new children
      foreach (var parent in cat.Select(x => x.CAT_ID))
            query = query.Union(categories.Where(x => x.CAT_PARENT = parent));
      var newchildren = query.ToList(); // execute query for all children at once - only once

      foreach (Category category in cats)
      {
            htmlOutput += "<li>";
            htmlOutput += category.CAT_NAME;
            // here we select only this category children - from already executed query results
            htmlOutput += html.CategoryTree(categories, newchildren.Where(x => x.CAT_PARENT = category.CAT_ID).ToList());
            htmlOutput += "</li>";
      }
}

Of course this is not final code, and you better use IN [parentid, parentid, parentid] instead of UNION, but that's the idea.