CODEWITHSUNDEEP
c#sqlasp.netasp.net-mvclinq
Steve
Steve

Reputation: 819

categories and sub-categories in c# / linq & sql

I am building an asp.net website with ms-sql db for products - each product belongs to one or more categories and each categories can belong to one or zero parent categories.

The user should be able to select zero or many categories, but i can't figure out a way to only return products in the selected categories.

i've got other filters which are working (minimum price, brand name etc) but cant' get the categories to work.

For example:

-Category 1
|---Sub-Category 1.1
    |---Sub-Sub-Category 1.1.1
    |---Sub-Sub-Category 1.1.2

|---Sub-Category 1.2

|---Sub-Category 1.3

If Category 1 is selected, then all products that have a category where the Ultimate Parent is Category 1 should be returned.

If Sub-Category 1.1 and Sub-Category 1.2 is selected then all products that have a category where the Ultimate Parent is either Sub-Category 1.1 OR Sub-Category 1.2 should be returned.

This is my Code:

Product:

public class Product
{
    [Key]
    public int ProductID { get; set; }
    public string Description {get;set;}
    public double Price {get;set;}

    public virtual List<Category> Categories { get; set; }

    public Product()
    {
       Categories = new List<Category>();
    }

}

Category:

public class Category
{
    [Key]
    public int CategoryID { get; set; }
    public string CategoryName { get; set; }

    public int? ParentCategoryID { get; set; }

    [JsonIgnore]
    public Category Parent { get; set; }

    [JsonIgnore]
    public virtual List<Product> Products { get; set; }

}

Simplified WebApi Controller:

public object Get(  [FromUri] string[] categories)
{
    List<Product> search_results = (from p in db.Products  
        where 1==1
              && p.Price >= minPrice && p.Price <=maxPrice 
             // only return products in selected categories

      select p).ToList();

}

Upvotes: 0

Views: 2408

Answers (1)

py3r3str
py3r3str

Reputation: 1879

If you have no limits on sub categories, you can use recursion:

public object Get(  [FromUri] string[] categories)
{
    var categories = db.Categories.Where(c => categoriesIds.Contains(c.Id));
    Func<Product, bool> filters = p => 1==1
          && p.Price >= minPrice && p.Price <=maxPrice 
    return GetCategoryiesProducts(categories, filters)
}

public IList<Product> GetCategoryiesProducts(IList<Category> categories, Func<Product, bool> filters)
{
    var result = new List<Product>();
    foreach (var c in categories) 
    {
        result.AddRange(c.Products.Where(filters).ToList());
        var subCategories = db.Categories.Where(s => s.ParentCategoryID != null && (int)s.ParentCategoryID == c.Id))
        if (subCategories != null && subCategories.Count > 0)
        {
             result.AddRange(GetCategoryiesProducts(subCategories, filters))
        }
    }
    List<Product> search_results result;
}

but if there be a lot of categories it will be expensive solution. To optimize for efficiency you can add product to each parent categories to eliminate recursion:

List<Product> search_results = (from p in db.Products  
where 1==1
    && p.Price >= minPrice && p.Price <=maxPrice 
    && p.Categories.Any(c => categoriesIds.Contains(c.Id))
select p).ToList();

Upvotes: 1

Related Questions