Linq to SQL C# Get Products in all child/sub child Categories too slow

asp.net, c#, linq, linq-to-sql

Solution

What is the difference between

var products = 
    (
     from p in db2.products_infos
     where  p.IsEnabled == true
     group p by p.ProdID
     into g where g.Any(x => catsChildList.Contains(Convert.ToInt32(x.CategoryID)))
     select g
    );

and

 var tmpList = 
    (
     from p in db2.products_infos
     where  p.IsEnabled == true 
            && catsChildList.Contains(Convert.ToInt32(p.CategoryID))) 
     select p
    ).ToList();

 var products = 
     (from r in tmpList group p by r.ProdID into g select g);

?

Maybe my schema is different, but when I try this with Linq2Sql it seems to return the same results, but the latter returns the results all in one database hit, whereas the former issues multiple requests. (If the number of products returned is 10000, it would be doing 10001 database requests).

Problem

What would be the best way to get all the products in all the child categories of a selected main category? Trying to get 10000 products in about 80 child categories but this method is too slow. Any suggestions? Using c# and Linq to SQL ``` //list for the child categories private List<int> catsChildList = new List<int>(); GetChildCats(123); //get all the child categories of main category '123' private void GetChildCats(int _parentCat) { var cats = (from c in db2.tbl_cart_categories where c.ParentID == _parentCat select new { c.CategoryID }); if (cats.Count() > 0) { foreach (var cat in cats) { int _cat = Convert.ToInt32(cat.CategoryID); catsChildList.Add(_cat); GetChildCats(_cat); } } } //Get the products var products = (from p in db2.products_infos where p.IsEnabled == true group p by p.ProdID into g where g.Any(x => catsChildList.Contains(Convert.ToInt32(x.CategoryID))) ``` takes about 6 seconds to return results

Original source