I need to find all products in specified categories. For example, user enters "woman, shoes, adidas" in filtering textbox, then I need to find all woman shoes from Adidas, so that get a list of products where each product included in all categories user wrote (logical AND).
I started with some linq query that didn't work, then after changes I still was getting different kinds of errors. Seems like NHibernate linq provider doesn't support methods (e.g.string.IsNullOrEmpty) in lambda expression, but more important it fails with complex queries... I ended up to call .AsIEnumerable() on sub-queries to force getting data from sql server in a simple way and moved other processing on web server side...
Please take a look at one of current query below which is of course not efficeint, but doesn't generate exception at least. But it gives me zero results while I know there are a lot of products in categories I am testing.
// These are the categories our products must be present in string[] categoryNames = { "Software", "Support" }; var query = from product in Product.All() where product.CategoryProductRelations.Where(x => categoryNames.Contains(x.Category.Name)).Count() == categoryNames.Count() select product;
It basically looks for the categories specified in the categoryNames array and for each product counts whether the number matched is the same as the number we're looking for.
And I would like to mention couple of things just to share some of my exprerinces.
First, if categoryNames is for example IEnumerable instead of array, then it doesn't work as expected. Second, is that when I added next additional filtering to where clause
then code doesn't fail, but works incorrectly - doesn't filter products.
So it is just remind that Linq to Something is not so always so sweet like Linq to Objects :). Therefore, developer have to sort out things and find out limitations of particular linq provider.
Version 1.2 will include upgraded LINQ provider. If you're interested I can send you a prerelease version to play with if you want to try it out. Shoot me an e-mail at [email protected]
We've been using the new provider internally for a while now.
How to get products in specified categories
Hi,
I need to find all products in specified categories. For example, user enters "woman, shoes, adidas" in filtering textbox, then I need to find all woman shoes from Adidas, so that get a list of products where each product included in all categories user wrote (logical AND).
I started with some linq query that didn't work, then after changes I still was getting different kinds of errors. Seems like NHibernate linq provider doesn't support methods (e.g.string.IsNullOrEmpty) in lambda expression, but more important it fails with complex queries... I ended up to call .AsIEnumerable() on sub-queries to force getting data from sql server in a simple way and moved other processing on web server side...
Please take a look at one of current query below which is of course not efficeint, but doesn't generate exception at least. But it gives me zero results while I know there are a lot of products in categories I am testing.
IEnumerable<int> categoriesIds = Category.All().AsEnumerable<Category>()
.Where<Category>((c => categoriesNames.Contains(c.Name.Trim().ToLower())))
.Select<Category, int>(c => c.CategoryId);
var filteredProducts = from p in Product.All().AsEnumerable<Product>()
let productCategories = p.CategoryProductRelations.Select<CategoryProductRelation, int>(cpr => cpr.CategoryId)
where /*p.ParentProductId == null && (p.VariantSku == null || p.VariantSku == "")
&&*/ productCategories.OrderBy(id => id).SequenceEqual(categoriesIds.OrderBy(id => id))
orderby p.Name ascending
select p;
Could someone please help me this issue? Maybe I missing soemthing, and I am using API in wrong way?
Maybe it is EntitiesV2 issue, and with subsonic version it is possieblt to compose needed query?
Thanks in advcance.
Hi Slava,
Your question inspired me to do a post on how to do put together the query. I've reposted the contents here for your convenience:
It basically looks for the categories specified in the categoryNames array and for each product counts whether the number matched is the same as the number we're looking for.
Hi Søren,
Thank you. Your proposed query has helped me.
And I would like to mention couple of things just to share some of my exprerinces.
First, if categoryNames is for example IEnumerable instead of array, then it doesn't work as expected. Second, is that when I added next additional filtering to where clause
p.ParentProductId == null && (p.VariantSku == null || p.VariantSku == "")
then code doesn't fail, but works incorrectly - doesn't filter products.
So it is just remind that Linq to Something is not so always so sweet like Linq to Objects :). Therefore, developer have to sort out things and find out limitations of particular linq provider.
Which version of uCommerce are you running?
It is 1.1.1.0 version.
Version 1.2 will include upgraded LINQ provider. If you're interested I can send you a prerelease version to play with if you want to try it out. Shoot me an e-mail at [email protected]
We've been using the new provider internally for a while now.
Thanks, but I managed to get needed functionality working for now, so it is ok as it is now.
So, is there a more uCommerce-y way of doing this with 2.0?
I'm using this (added to a list to work with my paging functions):
var myCategories = Category.All().Where(x => x.DisplayOnSite && (x.CategoryId == int.Parse(myCategoryID) || x.ParentCategoryId == int.Parse(myCategoryID))); foreach (var myCategory in myCategories) { myProductsAll = Product.GetForCategory(cat).Where(x => x.ParentProductId == null && x.DisplayOnSite && x.AllowOrdering); productList.AddRange(myProductsAll.ToList()); }I'm sure that loop should not be necessary
You could do a join to avoid the loop.
Thankfully, the client has changed their mind, but that is how I would do it now.
is working on a reply...