- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
building a predicate expression containing a subquery
Joined: 16-Jul-2010
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
Joined: 16-Jul-2010
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
Joined: 16-Jul-2010
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