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

Question

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

Answer 1

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).