What would be the best way to get all the products in all the child categories of a selected main category?

Question

This is my Class File Structure:

public partial class Category
{
   public int Id { get; set; }
   public string Name { get; set; }
   public int ParentCategoryId { get; set; } //reference to Id 
   public ICollection<Category> _subcategories;
} 

public partial class ProductCategory
{
   public int Id { get; set; }
   public int ProductId { get; set; }
   public int CategoryId { get; set; }
   public virtual Category Category { get; set; }
   public virtual Product Product { get; set; }
}

public partial class Product
{
   public int Id { get; set; }
   public string Name { get; set; }                     
  public ICollection<ProductViewMap> _productViewmap;

}

public class ProductViewMap
{
public int ProductId { get; set; }
public int ProductViewCount { get; set; }//indicated how many times product has been viewed means most popular product.
public virtual Product Product { get; set; }
}

This is what i have tried:

//List to hold all Category Ids of Parent Category Id say for eg:1
List<int> categoryChildList = new List<int>();
var data = (from temp in context.Category
            where temp.ParentCategoryId == parentCategoryId
            select new { CategoryId = temp.Id });
if(data.Count() > 0)
{
    foreach (var cat in data)
    {
        int _cat = Convert.ToInt32(cat.CategoryId);
        categoryChildList.Add(_cat);
    }
    var tmpList = (from p in Context.ProductCategory
                    join m in context.Product on p.ProductId equals m.Id
                    join n in context.ProductViewMap on m.Id equals n.ProductId
                    where categoryChildList.Contains(p.CategoryId)
                    select m).ToList();

Here error is coming:

Object reference not set to instance of object.**

When i am removing this line then everything works fine:

join n in context.ProductViewMap on m.Id equals n.ProductId

any help would be greatly appreciated.

Sql fiddle which contain sample records:http://www.sqlfiddle.com/#!3/bde6b

If Input is :Computer(parentCategoryId:1) then output is as below

Final output:

ProductId ProductName

1 hp

2 compaq

3 lenovo

possible duplicate of [What is a NullReferenceException and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Jeroen Vannevel, Dec 26 '14 at 15:31
It's a `NullReferenceException`. It's been written about so much already, even the bible has references to it. Your question has nothing to do with the problem, your code won't compile (`Context` and `context`) and we can't reproduce it. — Jeroen Vannevel, Dec 26 '14 at 15:34
sorry context and Context are the same.thats not the problem. — Maria Pithia, Dec 26 '14 at 15:38
but thats what i am asking.when i remove this line:"join n in context.ProductViewMap on m.Id equals n.ProductId" then everything wokrs fine.so if possible can anybody provide me solution as because this error is in query and i am not getting it — Maria Pithia, Dec 26 '14 at 15:40
Is there a collection on `Product` that connects it to `ProductViewMap`? — Corey Adler, Dec 26 '14 at 15:55
@IronMan84:please see the edited question and if you have another solution or if you can edit current solution then i would be so thankfull to you as i am badly struck on this.please — Maria Pithia, Dec 26 '14 at 16:15
possible duplicate of [how do display top 5 products from specific category](http://stackoverflow.com/questions/27645941/how-do-display-top-5-products-from-specific-category) — Corey Adler, Dec 26 '14 at 16:43

Corey Adler · Accepted Answer · 2014-12-26T16:50:36.033

0

If all you're trying to do is to grab the Product records, then using the Extension Method syntax you could do it this way:

var products = context.ProductCategory.Where(pc => pc.Category.ParentCategoryID != null && pc.Category.ParentCategoryID == parentCategoryID)
                                      .Select(pc => pc.Product)
                                      .Distinct()
                                      .ToList()
                                      .OrderBy(p => p.ProductViewMap.Max(pvm => pvm.ProductViewCount);

All of the joins will be taken care of by the SQL query generated by LINQ to Entities.

edited Dec 26 '14 at 16:50

answered Dec 26 '14 at 16:19

Corey Adler

15,897
18
66
80

i think you havent understand the question.i want to get all the products in all the child categories of a selected main category(Parent category) and this you will get from ProductViewMap table where you will get the product which is maximum viewed that is productviewcount. – Maria Pithia Dec 26 '14 at 16:33
Please see my edited question which contain sql fiddle link to show you sample records and expected output which i have given in my question. – Maria Pithia Dec 26 '14 at 16:43
Your question is on getting the Products. Do you want them sorted by their View Count? – Corey Adler Dec 26 '14 at 16:48
yup i want top say 5 products whose ProductViewCount is more but under specific category say for eg:"computer" and sir this is not a duplicate question as here i have asked what is the best way to get all the products in all the child categories of a selected main category(Parent category).so this is not a duplicate question – Maria Pithia Dec 27 '14 at 02:37
Sir when i say that get top 5 products of computer but problem is that some products are not directly under Computer they are in their child category (eg Laptop) as you can see from sql fiddle records and from my expected output – Maria Pithia Dec 27 '14 at 02:41

What would be the best way to get all the products in all the child categories of a selected main category?

1 Answers1