public static List <Product> FindProductsByCategory(int categoryId, ProductSortByField sortBy) { ProductQuery p = new ProductQuery("p"); ProductCategoryQuery pc = new ProductCategoryQuery("pc"); vProductsSoldCountsQuery productsSold = new vProductsSoldCountsQuery("productsSold"); p.Select(p); p.InnerJoin(pc).On(p.Id == pc.ProductId); p.LeftJoin(productsSold).On(p.Id == productsSold.ProductId); p.Where(p.IsActive == true); p.Where(pc.CategoryId == categoryId); if (!string.IsNullOrEmpty(sortBy.Field)) { p.OrderBy(sortBy.Field, sortBy.SortDirection == SortDirection.ASC ? esOrderByDirection.Ascending : esOrderByDirection.Descending); } //string sql = p.Parse(); ProductCollection products = new ProductCollection(); products.Load(p); return(products.Where(z => z.IsViewable == true).ToList()); }
protected void InitQuery(vProductsSoldCountsQuery query) { query.OnLoadDelegate = this.OnQueryLoaded; if (!query.es2.HasConnection) { query.es2.Connection = ((IEntityCollection)this).Connection; } }
public static List <Product> FindProductsByCategories(IList <int> categoryIds, ProductSortByField sortBy, bool matchAllCategories) { if (categoryIds.Count > 0) { ProductQuery p = new ProductQuery("p"); ProductCategoryQuery pc = new ProductCategoryQuery("pc"); vProductsSoldCountsQuery productsSold = new vProductsSoldCountsQuery("productsSold"); //p.es.CountAll = true; //p.es.CountAllAlias = "TotalResultCount"; //p.es.PageNumber = 1; //p.es.PageSize = 2; p.es.Distinct = true; p.Select(p); p.InnerJoin(pc).On(p.Id == pc.ProductId); p.LeftJoin(productsSold).On(p.Id == productsSold.ProductId); p.Where(p.IsActive == true); if (matchAllCategories) { ProductCategoryQuery pcAllCats = new ProductCategoryQuery("pcAll"); pcAllCats.Select(pcAllCats.ProductId) .Where(pcAllCats.CategoryId.In(categoryIds.ToArray())) .GroupBy(pcAllCats.ProductId) .Having(pcAllCats.ProductId.Count() == categoryIds.Count); p.Where(p.Id.In(pcAllCats)); } else { p.Where(pc.CategoryId.In(categoryIds.ToArray())); } if (!string.IsNullOrEmpty(sortBy.Field)) { p.OrderBy(sortBy.Field, sortBy.SortDirection == SortDirection.ASC ? esOrderByDirection.Ascending : esOrderByDirection.Descending); } string sql = p.Parse(); ProductCollection products = new ProductCollection(); products.Load(p); return(products.Where(z => z.IsViewable == true).ToList()); } return(new List <Product>()); }
public bool Load(vProductsSoldCountsQuery query) { this.query = query; InitQuery(this.query); return(Query.Load()); }