building a predicate expression containing a subquery

Posts   
 
    
colmdoc
User
Posts: 3
Joined: 16-Jul-2010
# Posted on: 16-Jul-2010 16:04:21   

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

colmdoc
User
Posts: 3
Joined: 16-Jul-2010
# Posted on: 16-Jul-2010 18:23:56   

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

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 17-Jul-2010 04:41:01   

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

David Elizondo | LLBLGen Support Team
colmdoc
User
Posts: 3
Joined: 16-Jul-2010
# Posted on: 17-Jul-2010 12:07:06   

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

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 19-Jul-2010 06:06:13   

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.

David Elizondo | LLBLGen Support Team