How to search in subcategories by giving a 'head' category in MVC4

Question

I'm trying to add a search engine to my site, where you can search products by choosing a category. The category tree has an architecture like this:

• HeadCategory1
  • SubCategory1
    • SubSubCategory1
    • SubSubCategory2
  • SubCategory2
• HeadCategory2
  • SubCategory3
• HeadCategory3

In the search form you can only choose from the HeadCategories (pulled from the db). How can I create a (recursive) function that searches in the HeadCategory you choose ánd in the subcategories and subsubcategories of that headcategory?

My search code looks like this:

[HttpPost]
    public ActionResult Search(SearchCriteria model, int? page)
    {
        UnitOfWork _uow = new UnitOfWork();

        int pageNumber = page ?? 1;

        var query = _uow.ProductRepository.Get(
            includeProperties: "Author"
        );

        // Search terms
        string[] keywords = new string[1];
        if (model.Keywords != null && model.Keywords.Length > 0 && model.Keywords.Contains(' '))
            keywords = model.Keywords.Split(' ');
        else
            keywords[0] = model.Keywords;

        if (keywords[0] != null)
        {
            foreach (string word in keywords)
            {
                query = query.Where(p => p.Description.Contains(word) || p.Name.Contains(word));
            }
        }

        if (model.CategoryId > 0 && query.Count() > 0)
        {
            // HERE IS WHERE THE CHANGES SHOULD BE MADE
            query = query.Where(r => r.CategoryId == model.CategoryId);
        }

        List<Product> zipResults = query.ToList();
        RangeResult[] postcodes;
        int postcode;
        if (model.Distance > 0 && int.TryParse(model.Zipcode.Substring(0, 4), out postcode))
        {
            // Calls a function that uses a SOAP service
            postcodes = range(postcode, model.Distance);
            foreach(Product result in query)
            {
                if (result.Author.Zipcode == null)
                    continue;
                foreach (RangeResult rr in postcodes)
                {
                    if (result.Author.Zipcode.Contains(rr.nl_fourpp.ToString()))
                        zipResults.Add(result);
                }
            }
        }

        //results = results.ToPagedList(pageNumber, PAGE_SIZE);
        return View("Resultaten", new SearchResultsVM
        {
            Products = zipResults.ToList().ToPagedList(pageNumber, PAGE_SIZE),
            Count = zipResults.Count()
        });

Also, I guess this search engine code isn't at its best, so if you have some tips/improvements please share with me.

EDIT: my category model looks like this:

public class Category
{
    [Key]
    public int Id { get; set; }

    public string Name { get; set; }

    public string Description { get; set; }

    public int? ParentId { get; set; }
    public virtual Category Parent { get; set; }

    public virtual ICollection<Category> Subcategories { get; set; }

    public decimal PostCosts { get; set; }

    public byte[] Image { get; set; }

    public virtual ICollection<Product> Products { get; set; }

    public string Slug { get; set; }

    public override string ToString()
    {
        return Name;
    }
}

EDIT: Is it smart to just add an extra column in the category table, giving it the id of the 'head' category of that specific category? Or maybe an extra table that combines the category id with it's corresponding head category?

Answer 1

Consider doing the heavy lifting directly in your database. If you're using SQL Server, you can create a recursive search function using Common Table Expressions.

Keeping this search code in the database means it will be easier to fine-tune the query for performance purposes.

Assuming you're using an ORM like Entity Framework, you can call this new database logic like so:

var results = context.ExecuteStoreQuery<Product>("exec MySearchStoredProc").ToList();