nicolas.muniere wrote:...
ProductLoadAllPaged should be used only for search page.
...
I agree with Nicolas. I just played with LinqPad rewriting some queries in just Linq and the generated SQL is good.
void Main()
{
string connString = "Data Source=SERVER;Initial Catalog=nopCommerce_330_sample;Integrated Security=False;Persist Security Info=False;User ID=UID;Password=PWD;MultipleActiveResultSets=True";
var dbContext = new Nop.Data.NopObjectContext(connString);
var _productRepository = new Nop.Data.EfRepository<Product>(dbContext);
var products = _productRepository.Table;
var _storeMappingRepository = new Nop.Data.EfRepository<StoreMapping>(dbContext);
var storeMappings = _storeMappingRepository.Table;
int storeId = 0;
int categoryId = 2;
// var query =
// from p in products
// where !p.Deleted
// && p.Published
// && p.VisibleIndividually
// select p;
var query = products.Where(p => !p.Deleted && p.Published && p.VisibleIndividually);
query = query.Where(p => p.ProductCategories.Any(pc => pc.Id == categoryId));
//query = query.Where(p => p.ProductCategories.Any(pc => new List<int>(){2,3,4}.Contains(pc.Id) )); //e.g. include sub categories
//query = query.Where(p => p.ProductCategories.Any(pc => pc.Id == 2 && pc.IsFeaturedProduct));
//store mapping
// query = query.Where(p => !p.LimitedToStores
// || (storeMappings.Any(sm => sm.EntityId == p.Id && sm.EntityName == "Product" && sm.StoreId == storeId))
// );
query.Take(15).Dump();
//numberOfProducts
var filteredProducts = products
.Where(p => !p.Deleted && p.Published && p.VisibleIndividually)
.Where(p => p.ProductCategories.Any(pc => pc.Id == categoryId));
// this can be optionally included only if multi-store (the nested ".Any()" becomes a "AND EXISTS (SELECT ..." )
if (storeId > 0)
{
filteredProducts = filteredProducts
.Where(p => !p.LimitedToStores
|| (storeMappings.Any(sm => sm.EntityId == p.Id && sm.EntityName == "Product" && sm.StoreId == storeId)));
}
int numberOfProducts = filteredProducts.Count();
numberOfProducts.Dump("numberOfProducts");
//--------
}
Here are my notes / snippets from the nopC code; you can see that several calls to SearchProducts can be replaced by using bits from above.
// FROM ProductLoadAllPaged sp
//
// AND (p.LimitedToStores = 0 OR EXISTS (
// SELECT 1 FROM [StoreMapping] sm with (NOLOCK)
// WHERE [sm].EntityId = p.Id AND [sm].EntityName = ''Product'' and [sm].StoreId=' + CAST(@StoreId AS nvarchar(max)) + '
// ))'
//
// AND p.VisibleIndividually = 1
//
// LEFT JOIN Product_Category_Mapping pcm with (NOLOCK)
// ON p.Id = pcm.ProductId
//
// AND pcm.CategoryId
//
//
//
//\Presentation\Nop.Web\Controllers\CatalogController.cs
// protected int GetCategoryProductNumber(int categoryId)
// var categoryIds = new List<int>();
// categoryIds.Add(categoryId);
// //include subcategories
// if (_catalogSettings.ShowCategoryProductNumberIncludingSubcategories)
// categoryIds.AddRange(GetChildCategoryIds(categoryId));
// var numberOfProducts = _productService
// .SearchProducts(categoryIds: categoryIds,
// storeId: _storeContext.CurrentStore.Id,
// visibleIndividuallyOnly: true,
// pageSize: 1)
// .TotalCount;
// return numberOfProducts;
//
// public ActionResult Category(int categoryId, CatalogPagingFilteringModel command)
// featuredProducts = _productService.SearchProducts(
// categoryIds: new List<int>() { category.Id },
// storeId: _storeContext.CurrentStore.Id,
// visibleIndividuallyOnly: true,
// featuredProducts: true);
//
// public ActionResult RecentlyAddedProducts()
// public ActionResult RecentlyAddedProductsRss()
// var products = _productService.SearchProducts(
// storeId: _storeContext.CurrentStore.Id,
// visibleIndividuallyOnly: true,
// orderBy:ProductSortingEnum.CreatedOn,
// pageSize:_catalogSettings.RecentlyAddedProductsNumber);
//
//
// public ActionResult ProductsByTag(int productTagId, CatalogPagingFilteringModel command)
// var products = _productService.SearchProducts(
// storeId: _storeContext.CurrentStore.Id,
// productTagId: productTag.Id,
// visibleIndividuallyOnly: true,
// orderBy: (ProductSortingEnum)command.OrderBy,
// pageIndex: command.PageNumber - 1,
// pageSize: command.PageSize);
//
//\Presentation\Nop.Web\Controllers\CommonController.cs
// var products = _productService.SearchProducts(storeId: _storeContext.CurrentStore.Id,
// visibleIndividuallyOnly: true,
// pageSize: 200);
//
//
//\Presentation\Nop.Web\Administration\Controllers\ProductController.cs
// public ActionResult DownloadCatalogAsPdf()
// var products = _productService.SearchProducts(vendorId: vendorId, showHidden: true);
//
//