Variant Query

Question

Using the September 2009 VPC I am trying to do the following query give me all child products of the parka category who's ProductColor is Green. Searching the forum I found an example that did a Rating query on supplies but the Rating property was a custom property not a variant property. Below is the code from a console app. Can anyone tell me why this returns no results when there is in fact Parkas that are green in color.

{
            OperationServiceAgent commerceProxy = new OperationServiceAgent();

            CommerceRequestContext commerceContext = requestContext();



            var query = new CommerceQuery<CommerceEntity>("Category");

            query.SearchCriteria.Model.Properties["CatalogId"] = "Adventure Works Catalog";

            query.SearchCriteria.Model.Id = "Parka";



            // add child products, recursive

            {

                // query children products returned along with the category.

                var queryProducts = new CommerceQueryRelatedItem<CommerceEntity>("ChildProducts", "Product");

                queryProducts.SearchCriteria.WhereClause = "[ProductColor]= 'Green'";



                queryProducts.QueryOptions.IsRecursive = true;



                // add variants to the child products

                {

                    var queryVariants = new CommerceQueryRelatedItem<CommerceEntity>("Variants", "Variant");

                    queryVariants.SearchCriteria.WhereClause = "[ProductColor] = 'Green'";
                    
                    queryProducts.RelatedOperations.Add(queryVariants);

                }



                query.RelatedOperations.Add(queryProducts);

            }



            CommerceResponse response = commerceProxy.ProcessRequest(commerceContext, query.ToRequest());

            var queryResponse = response.OperationResponses[0] as CommerceQueryOperationResponse;

        }

Answer 1

Hi,

Is green a property on both the Product and Variant?  I would also recommend possibly using a strictly typed approached to these queries instead of a Where statement - such as :

var queryProducts = new CommerceQueryRelatedItem<CommerceEntity>("ChildProducts", "Product");
queryProducts.SearchCriteria.Model.Properties["ProductColor"]= "Green";
<br/><br/>

Let me know how you get on...

Thanks,
Lewis

---

Follow Me on Twitter: @LewisBenge Or check out my blog: http://www.geekswithblogs.com/pointtoshare/

Answer 2

ProductColor is a variant property. I even tried the following and it returns all the variants not just the ones green in color.

var queryProducts = new CommerceQuery<CommerceEntity>("Product");
                queryProducts.SearchCriteria.Model.Properties["CatalogId"] = "Adventure Works Catalog";
                queryProducts.SearchCriteria.Model.Properties["Id"] = "AW029-03";
                queryProducts.SearchCriteria.Model.Properties[Product.PropertyName.ParentId] = "Shirts";
                
                {

                    var queryVariants = new CommerceQueryRelatedItem<Variant>("Variants");
                    queryVariants.Model.Properties.Add("Id");
                    queryVariants.Model.Properties.Add("ProductColor");
                    queryVariants.SearchCriteria.Model.Properties["ProductColor"] = "Green";
                    //queryVariants.SearchCriteria.WhereClause = "[ProductColor] = Green";
                    queryVariants.QueryOptions.IsRecursive = false;
                    queryProducts.RelatedOperations.Add(queryVariants);

                }

When I look at the above I expect to get all variants that have a color of green. Instead it returns all the variants regardless of the product color I specified. I have tried both queryVariants.Model.Properties["ProductColor"] and queryVariants.SearchCriteria.WhereClause

What am I missing is this a bug, am I not understanding the foundation API.

Answer 3

Ok all indications are that the whereclause on a variant query has no affect when the property in the where clause is a variant property.

The following CommerceCatalogFullTextSearchBuilder code returns four commerce entities. One for the product and three variants for each variant of the product that is green in color, which is what I was after.

var query = new CommerceQuery<CatalogEntity, CommerceCatalogFullTextSearchBuilder>();

query.SearchCriteria.Catalogs.Add("Adventure Works Catalog");

query.SearchCriteria.CategoriesClause = "[CategoryName] = 'Shirts'";

query.SearchCriteria.WhereClause = "[ProductId] = 'AW029-03' and [ProductColor] = 'Green'";