Yes that would still be the way to do it. We looked into it, but it was too complicated fix to be done.

Performance wise you might want to switch to index based search provider.

When I try to do my catalog query like this, I get no results:

CatalogSearchParameters pars = new CatalogSearchParameters();
CatalogSearchOptions options = new CatalogSearchOptions
{
CacheResults = false,
RecordsToRetrieve = 10
};

options.Classes.Add('OurVariation');
options.Classes.Add('OurPackage');

pars.Language = "fr";

pars.SqlMetaWhereClause = "(META.MetaFieldName = 'OurWebStatus' AND META.Number <> 578)";

pars.SqlWhereClause = "(MetaClassId=28 OR MetaClassId=29) AND IsActive = 'true'";

pars.CatalogNames.Add("OurShop");

Entries tmpEntries = CatalogContext.Current.FindItems(pars, options, new CatalogEntryResponseGroup());

When I remove my MetaWhereClause, I get results. So it's seems the problem is in the MetaWhereClause/Classes.

Here's the request I saw in my SQL Server Profiler when I try to call the FindItems function above:

declare @p17 int
set @p17=-1
exec ecf_CatalogEntrySearch @SearchSetId='87FA5F2F-68A4-4F03-ABE7-8DF5339A83DD',@Language=N'fr',@Catalogs=N'OurShop',@CatalogNodes=N'',@SQLClause=N'(MetaClassId=28 OR MetaClassId=29) AND IsActive = ''true''',@MetaSQLClause=N'
INNER JOIN
(SELECT META.ObjectId
FROM CatalogContentProperty META
INNER JOIN MetaClass MC ON MC.MetaClassId = META.MetaClassId
LEFT JOIN MetaKey MK ON META.Number = MK.MetaKey
LEFT JOIN MetaMultiValueDictionary mmvd ON mk.MetaKey = mmvd.MetaKey
LEFT JOIN MetaDictionary METADIC ON mmvd.MetaDictionaryId = METADIC.MetaDictionaryId AND mk.MetaFieldId = METADIC.MetaFieldId
WHERE META.ObjectTypeId = 0
AND (META.MetaFieldName = ''OurWebStatus'' AND META.Number <> 578) AND MC.Name IN (SELECT Item FROM ecf_splitlist('''''' + OurVariation,OurPackage + '''''')) ) FilteredEntries
ON FilteredEntries.ObjectId = [CatalogEntry].CatalogEntryId ',@KeywordPhrase=N'',@OrderBy=N'',@Classes=N'OurVariation,OurPackage',@StartingRec=0,@NumRecords=10,@JoinType=N'',@SourceTableName=N'',@TargetQuery=N'',@SourceJoinKey=N'',@TargetJoinKey=N'',@RecordCount=@p17 output,@ReturnTotalCount=1
select @p17

By the way, I renamed some of our fields with the 'our' prefix for this example instead of the company name.

Can you spot what's wrong with the query that I don't have any results?

When I do this query, I have 7786 rows:

SELECT *
FROM [eCommerce].[dbo].[CatalogContentProperty] as META
INNER JOIN MetaClass MC ON MC.MetaClassId = META.MetaClassId
WHERE(MetaFieldName = 'OurWebStatus' AND Number <> 578)
AND(MC.[Name] = 'OurVariation' OR MC.[Name] = 'OurPackage')

Update: Seems like a bug when we use options.Classes.Add to use with the pars.SqlMetaWhereClause.

When I see the generated request from ecf_Search_CatalogEntry in Sql Server Profiler, I get:

with OrderedResults as (SELECT count([CatalogEntry].CatalogEntryId) OVER() TotalRecords, [CatalogEntry].CatalogEntryId, ROW_NUMBER() OVER(ORDER BY CatalogEntry.CatalogEntryId) RowNumber FROM [CatalogEntry] CatalogEntry 
INNER JOIN 
(SELECT META.ObjectId 
FROM CatalogContentProperty META 
INNER JOIN MetaClass MC ON MC.MetaClassId = META.MetaClassId 
LEFT JOIN MetaKey MK ON META.Number = MK.MetaKey 
LEFT JOIN MetaMultiValueDictionary mmvd ON mk.MetaKey = mmvd.MetaKey 
LEFT JOIN MetaDictionary METADIC ON mmvd.MetaDictionaryId = METADIC.MetaDictionaryId AND mk.MetaFieldId = METADIC.MetaFieldId 
WHERE META.ObjectTypeId = 0 
AND (META.MetaFieldName = 'OurWebStatus' AND META.Number <> 578) AND MC.Name IN (SELECT Item FROM ecf_splitlist(''' + OurVariation,OurPackage + ''')) ) FilteredEntries 
ON FilteredEntries.ObjectId = [CatalogEntry].CatalogEntryId INNER JOIN @Catalogs_temp catalogs ON CatalogEntry.CatalogId = catalogs.CatalogId INNER JOIN CatalogLanguage l ON l.CatalogId = CatalogEntry.CatalogId AND l.LanguageCode = N'fr' WHERE 1=1 AND ((MetaClassId=28 OR MetaClassId=29) AND IsActive = 1)) INSERT INTO @Page_temp (TotalRecords, ObjectId, SortOrder) SELECT top(10) TotalRecords, CatalogEntryId, RowNumber FROM OrderedResults WHERE RowNumber > 0;

The problem is : ecf_splitlist(''' + OurVariation,OurPackage + ''') 
If I replace it with ecf_splitlist('OurVariation,OurPackage'), it works.

So I think there's a bug when the call to ecf_Search_CatalogEntry is made.

The guy in this post(https://world.episerver.com/forum/developer-forum/Episerver-Commerce/Thread-Container/2017/9/episerve-commerce-10-catalog-product-search/) said he fixed his problem by removing his classes.add line.

If I try this, I get these errors when I call CatalogContext.Current.FindItems : 
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Exception in ecf_Search_CatalogEntry: Column 'Code' is constrained to be unique. 
Value '303205' is already present.Column 'Code' is constrained to be unique. 

Value '303205' is already present.Column 'Code' is constrained to be unique. 

...

And many more errors like that.

Is there a workaround or how can I do this catalog search with our meta fields? 
We already have a solr index but we use it after this cleanup is done and we can't use it to clean our catalog items.

Thanks, we will look into this. I don't know a workaround that work, but technically you can leave empty classes, and add this to SqlMetaWhereClause to have (META.MetaClassId = 12 OR META.MetaClassId = 13) AND ...

I haven't tried it, but it might work :) 

I finally got it.

I had to include a META.Language = 'en' in the SqlMetaWhereClause or else all my results were doubled and I had an exception.

So my SqlMetaWhereClause was : (META.LanguageName = 'en' AND META.MetaFieldName = 'OurWebStatus' AND META.Number <> 578)

And I removed all classes.

It's not great performance wise coz now I have to do 2 queries that return a lot more results than when they were combined and even if the sql query is quick, when the CatalogContext.Current.FindItems is called and returns 7500 items, it takes a couple of minutes to run even if the SQL was quick.

But at least I have a workaround.