This is a good question - I think this is the limitation of Commerce 9. There is no way to specify that (you can't query a row which have MetaFieldName is both BrandId and SubBrandId.

One work around is to have two searches, one for META.MetaFieldName = 'BrandId' AND META.LongString ='" + brandId and one for META.MetaFieldName = 'SubBrandId' AND META.LongString ='" + subBrandId + "', then you can Intersect the result. Not optimal, yes, but it should work.

I'll see if we can fix this - but perhaps the better overall solution is to switch to indexed search (Find should be really really good at this)

Hi Quan,

Thanks for the response.

Even when i am trying for the single metafield name then also i am getting no results.

parameters.SqlMetaWhereClause = " META.MetaFieldName = 'BrandId' AND META.LongString ='" + brandId + "'";

Can you please tell me which table it is using or how it is being retrieved so that i can go and verify in my db.

FYI i am having record in the my metaclass custom table.

BrandId SubBrandId
99            ZZZ
99            ZZT
99            ZZX
99            ZZY
99            ZZS

Thanks,

Brijesh

It'll be looking into CatalogContentProperty table for the Meta part.

Hi Quan,

Thank for the response this table name realy helped me to resolve the problem.

I am able to get he solution with your help and here is my code.

var parameters = new CatalogSearchParameters();
var options = new CatalogSearchOptions();
parameters.SqlMetaWhereClause = " MetaFieldName = 'BrandId' AND LongString ='" + brandId + "'";

//options.Classes.Add("ProductEntry");
options.RecordsToRetrieve = 10;
var result = CatalogContext.Current.FindItems(parameters, options, new CatalogEntryResponseGroup());

var brandIdResults = result.Entry;

parameters.SqlMetaWhereClause = " MetaFieldName = 'SubBrandId' AND LongString ='" + subBrandId + "'";
result = CatalogContext.Current.FindItems(parameters, options, new CatalogEntryResponseGroup());
var subBrandIdResults = result.Entry;

var totalResults = MatchingRecordExist(brandIdResults, subBrandIdResults);

************************************************************************

private static int MatchingRecordExist(Entry[] brandIdEntries, Entry[] subBrandEntries)
{
if (brandIdEntries!=null && brandIdEntries.Length > 0 && subBrandEntries!=null && subBrandEntries.Length > 0)
{
var result= brandIdEntries.Select(a => a.CatalogEntryId).Intersect(subBrandEntries.Select(b => b.CatalogEntryId));
return result.Count();
}
return -1;
}

Previously i was not able to get result because of the 

options.Classes.Add("ProductEntry");

When i commented this i am able to get the result.

Thanks,

Brijesh

Wonderfull! I was so glad to find this discussion. Helped me out guys.