Issue with meta query on web 8.5

Question

I am having with broker query in web 8.5, the fields names are getting lost while forming the query.

Query build using API:

var query = new Query();
var criteria = new List<Criteria>();
var bySchema = new ItemSchemaCriteria(300);
criteria.Add(new PublicationCriteria(8));
var templateCriteria = new ItemTemplateCriteria(4787);
criteria.Add(templateCriteria);
var validityStart = new CustomMetaValueCriteria(new CustomMetaKeyCriteria("ValidityStart"), DynConfig.GetCurrentCMSDate(), Criteria.GreaterThanOrEqual);
var validityEnd = new CustomMetaValueCriteria(new CustomMetaKeyCriteria("ValidityEnd"), DynConfig.GetCurrentCMSDate(), Criteria.LessThanOrEqual);
criteria.Add(CriteriaFactory.NotIn(CriteriaFactory.Or(validityStart, validityEnd)));
criteria.Add(bySchema);
query.Criteria = CriteriaFactory.And(criteria.ToArray());
var componentsUris = query.ExecuteQuery();

But, when I ran the profiler, I could see that ValidityStart and ValidityEnd are not coming the query.

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P0 int,@P1 int,@P2 int,@P3 datetime2,@P4 datetime2,@P5 int',N'select distinct TOP(@P0)
 itemmeta0_.PUBLICATION_ID as col_0_0_, itemmeta0_.ITEM_REFERENCE_ID as col_1_0_, itemmeta0_.ITEM_TYPE as col_2_0_,
 itemmeta0_.ITEM_REFERENCE_ID as col_3_0_ from ITEMS itemmeta0_ cross join COMPONENT componentm1_ inner join 
 ITEMS componentm1_1_ on componentm1_.PUBLICATION_ID=componentm1_1_.PUBLICATION_ID and 
 componentm1_.NAMESPACE_ID=componentm1_1_.NAMESPACE_ID and componentm1_.ITEM_REFERENCE_ID=componentm1_1_.ITEM_REFERENCE_ID 
 cross join COMPONENT_PRES_META_DATA componentp2_ where itemmeta0_.PUBLICATION_ID=@P1 and itemmeta0_.ITEM_REFERENCE_ID=componentp2_.COMPONENT_REF_ID and 
 itemmeta0_.PUBLICATION_ID=componentp2_.PUBLICATION_ID and componentp2_.COMPONENT_TEMPLATE_ID=@P2 and 
 (itemmeta0_.ITEM_REFERENCE_ID not in  (select distinct itemmeta3_.ITEM_REFERENCE_ID from ITEMS itemmeta3_
 cross join CUSTOM_META custommeta4_ where itemmeta3_.PUBLICATION_ID=custommeta4_.PUBLICATION_ID and itemmeta3_.ITEM_REFERENCE_ID=custommeta4_.ITEM_ID and itemmeta3_.ITEM_TYPE=custommeta4_.ITEM_TYPE and custommeta4_.KEY_DATE_VALUE>=@P3 or 
 itemmeta3_.PUBLICATION_ID=custommeta4_.PUBLICATION_ID and itemmeta3_.ITEM_REFERENCE_ID=custommeta4_.ITEM_ID 
 and itemmeta3_.ITEM_TYPE=custommeta4_.ITEM_TYPE and custommeta4_.KEY_DATE_VALUE<=@P4)) 
 and itemmeta0_.ITEM_REFERENCE_ID=componentm1_.ITEM_REFERENCE_ID and itemmeta0_.PUBLICATION_ID=componentm1_.PUBLICATION_ID 
 and componentm1_.SCHEMA_ID=@P5 order by itemmeta0_.ITEM_REFERENCE_ID ASC                                              
 ',300,8,4787,'2017-03-22 07:48:37.7670000','2017-03-22 07:48:38.2360000',4755
select @p1

Answer 1

I have faced same type issue with multiple CustomMetaValueCriteria. So to overcome that, I have changed my query like below. For me it is working fine now. Could you try the below code once -

This is Just a sample, you can change it according to your requirement -

Query query = new Query();
            ItemSchemaCriteria schemaCriteria = new ItemSchemaCriteria(300); PublicationCriteria publicationCriteria = new PublicationCriteria(8);
            ItemTemplateCriteria templateCriteria = new ItemTemplateCriteria(4787);
            CustomMetaValueCriteria validityStart = new CustomMetaValueCriteria(new CustomMetaKeyCriteria("ValidityStart"), DynConfig.GetCurrentCMSDate(), Criteria.GreaterThanOrEqual);
            CustomMetaValueCriteria validityEnd = new CustomMetaValueCriteria(new CustomMetaKeyCriteria("ValidityEnd"), DynConfig.GetCurrentCMSDate(), Criteria.LessThanOrEqual);

            Criteria[] criteriaList1 = { schemaCriteria, publicationCriteria, templateCriteria, validityStart };
            Criteria[] criteriaList2 = { schemaCriteria, publicationCriteria, templateCriteria, validityEnd };
            AndCriteria firstCriteria = CriteriaFactory.And(criteriaList1);
            AndCriteria secondCriteria = CriteriaFactory.And(criteriaList2);
            OrCriteria mainCriteriaCriteria = new OrCriteria(firstCriteria, secondCriteria);
            NotInCriteria finalCriteria = new NotInCriteria(mainCriteriaCriteria); 
query.Criteria = finalCriteria; 
var componentsUris = query.ExecuteQuery();

Let me know if it works fine for you.