HI folks

I am trying to fix an urgent bug in a system and wanted to generate the following query using a predicate filter

select * from AssessmentLocation AL
where AL.CountyId Is null 
AND AL.EstateId IS NULL 
AND AL.PropertyId Is NULL 
AND 
(
    SELECT Count(*) from AssessmentLocation AL2
    WHERE AL2.AssessmentId = AL.AssessmentId
) = 1

The predicate I have so far is the simple bit

predicateFilter.AddWithAnd(AssessmentLocationFields.CountyId == System.DBNull.Value);                       
predicateFilter.AddWithAnd(AssessmentLocationFields.EstateId == 
System.DBNull.Value);
predicateFilter.AddWithAnd(AssessmentLocationFields.PropertyId == System.DBNull.Value);                 

assesments.GetMulti(predicateFilter, 0, sortExpression, relations, null, pageIndex, pageSize);

My problem is I don't know how to add a subquery into apredicate filter Any suggestions would be appreciated

Thanks Col

I have revised my SQL to the following

SELECT * from AssessmentLocation AL
WHERE CountyId Is null 
AND EstateId IS NULL 
AND PropertyId Is NULL 
AND AssessmentId NOT IN
(
    SELECT  AssessmentId 
    from AssessmentLocation AL2
    WHERE   CountyId IS NOT NULL 
    OR      EstateId IS NOT NULL 
    OR      PropertyId IS NOT NULL  
)

My LLblgen code (v2.6) is now as follows

now what I need to figure out how to negate the SetOperator.In command ???

IPredicateExpression predicateSubquery = new PredicateExpression();

predicateSubquery.AddWithOr(AssessmentLocationFields.CountyId != System.DBNull.Value);
predicateSubquery.AddWithOr(AssessmentLocationFields.EstateId != System.DBNull.Value);
predicateSubquery.AddWithOr(AssessmentLocationFields.PropertyId != System.DBNull.Value);
                        
predicateFilter.AddWithAnd(AssessmentLocationFields.CountyId == System.DBNull.Value);
predicateFilter.AddWithAnd(AssessmentLocationFields.EstateId == System.DBNull.Value);
predicateFilter.AddWithAnd(AssessmentLocationFields.PropertyId == System.DBNull.Value);

predicateFilter.AddWithAnd(new FieldCompareSetPredicate( AssessmentLocationFields.AssessmentId, null, AssessmentLocationFields.AssessmentId, null, SetOperator.In, predicateSubquery));

If anybody sees any errors or improvements I would really appreciate the help

thanks col

Use the FieldCompareSetPredicate's ctor that accepts a "negate" parameter, and pass 'true' for it. Your implementation looks good

After looking up the documentation and this forum I found the following

For your information, NOT IN operator is not equal to <> ANY but is equal to <> ALL

I have modified my code accordingly

IPredicateExpression predicateSubquery = new PredicateExpression();
predicateSubquery.AddWithOr(AssessmentLocationFields.CountyId != System.DBNull.Value);
predicateSubquery.AddWithOr(AssessmentLocationFields.EstateId != System.DBNull.Value);
predicateSubquery.AddWithOr(AssessmentLocationFields.PropertyId != System.DBNull.Value);
                                                
predicateFilter.AddWithAnd(AssessmentLocationFields.CountyId == System.DBNull.Value);                       
predicateFilter.AddWithAnd(AssessmentLocationFields.EstateId == System.DBNull.Value);
predicateFilter.AddWithAnd(AssessmentLocationFields.PropertyId == System.DBNull.Value);
predicateFilter.AddWithAnd(new FieldCompareSetPredicate(AssessmentLocationFields.AssessmentId, null, AssessmentLocationFields.AssessmentId, null, SetOperator.NotEqualAll, predicateSubquery));

assesments.GetMulti(predicateFilter, 0, sortExpression, relations, null, pageIndex, pageSize);

However I am getting the following error when I run the code. This error also occurs for the SetOperator.In but not when I remove the FieldCompareSetPredicate?? I also get thsi error when I use the negate operator for SetOperator.In

[IndexOutOfRangeException: Index was outside the bounds of the array.]
   SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified) +3421
   SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause) +849
   SD.LLBLGen.Pro.DQE.SqlServer.SqlServerSpecificCreator.CreateSubQuery(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldPersistenceInfos, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32& uniqueMarker) +194
   SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.CreateSubQuery(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldPersistenceInfos, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, IGroupByCollection groupByClause, Int32& uniqueMarker) +87
   SD.LLBLGen.Pro.ORMSupportClasses.FieldCompareSetPredicate.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause) +409
   SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause) +698
   SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause) +698
   SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause) +698
   SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Int32& uniqueMarker) +38
   SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified) +3953
   SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause) +849
   SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) +183
   SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) +71
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGetMultiAction(ITransaction containingTransaction, IEntityCollection collectionToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, IPrefetchPath prefetchPathToUse, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) +1052
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetMulti(ITransaction containingTransaction, IEntityCollection collectionToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IEntityFactory entityFactoryToUse, IPredicate selectFilter, IRelationCollection relations, IPrefetchPath prefetchPathToUse, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) +81
   SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase`1.GetMulti(IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relations, IPrefetchPath prefetchPathToUse, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) +217
   SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase`1.GetMulti(IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relations, IPrefetchPath prefetchPathToUse, Int32 pageNumber, Int32 pageSize) +59
   RentPlus.BusinessLogic.AssessmentApprovalDataService.Assessments(Nullable`1 assessmentStatus, Nullable`1 regionId, Nullable`1 AssessmentLevel, String AssessmentName, Int32 pageIndex, Int32 pageSize, String sortExp

Again thanks for any suggestions or help with this.

Col

In above code you are using SetOperator.NotEqualAll not SetOperator.In. Please post the final version of the test. Better if you can reproduce it in Northwind DB.