Hi,

i'm trying to perform a query which does a condition with NOT EXIST. I've red about it in the documentation and it says to use FieldCompareSetPredicate, because EXIST is pretty much the same as IN.

This is a part of the SQL statement that i'm trying to use in my project:

AND NOT EXISTS (SELECT TransactionActivity.TransactionId 
                FROM TransactionActivity LEFT JOIN WorkerQueue ON TransactionActivity.EntityId=WorkerQueue.QueueItemId
                WHERE t.TransactionId = TransactionActivity.TransactionId
                AND WorkerQueue.CounterpartId = someId)

This is the part i have in my project and what does NOT work:

...
IPredicateExpression filter = new PredicateExpression();
...
IEntityRelation newSubQueryRelationActivity = new EntityRelation();           newSubQueryRelationActivity.AddEntityFieldPair(EntityFieldFactory.Create(TransactionActivityFieldIndex.EntityId),   
     EntityFieldFactory.Create(WorkerQueueFieldIndex.QueueItemId));
IRelationCollection subQueryRelationActivity = new RelationCollection();
subQueryRelationActivity.Add(newSubQueryRelationActivity, JoinHint.Left);
IPredicateExpression subQueryFilterActivity = new PredicateExpression();               subQueryFilterActivity.Add(PredicateFactory.CompareValue(TransactionFieldIndex.TransactionId, 
     ComparisonOperator.Equal, TransactionActivityFieldIndex.TransactionId, "t"));        subQueryFilterActivity.AddWithAnd(PredicateFactory.CompareValue(WorkerQueueFieldIndex.CounterpartId, 
     ComparisonOperator.Equal, recipientId));
filter.AddWithAnd(new FieldCompareSetPredicate(TransactionFields.TransactionId, null, 
     TransactionActivityFields.TransactionId, null, SetOperator.Exist, subQueryFilterActivity,  
     subQueryRelationActivity, true));
...

Is there someone who can help me to trnsform the sql code to c# code?

Regards,

Jeffrey

Here's a piece of code where this is functioning quite well :

'DEFINE EXISTS CLAUSE : use CompareExpression here existsFilter.Add(PredicateFactory.CompareExpression(TransactionFieldIndex.DepotId, ComparisonOperator.Equal, New Expression(EntityFieldFactory.Create(DepotFieldIndex.DepotId))))

'Add the SetOperator to the filter and add the complete existsFilter filter.Add(New FieldCompareSetPredicate(EntityFieldFactory.Create(TransactionFieldIndex.DepotId), EntityFieldFactory.Create(TransactionFieldIndex.DepotId), SetOperator.Exist, existsFilter, True))

You may also use the following Query instead:

AND t.TransactionId NOT IN 
(
  SELECT TransactionActivity.TransactionId 
  FROM TransactionActivity 
  LEFT JOIN WorkerQueue ON TransactionActivity.EntityId=WorkerQueue.QueueItemId
  WHERE WorkerQueue.CounterpartId = someId
)

And you would also use FieldCompareSetPredicate Please refer to FieldCompareSetPredicate in the LLBLGen Pro documentation manual "Using the generated code -> Adapter/SelfServicing -> Filtering and sorting -> The predicate system"

I had tried to use the IN clause, but i made a mistake with it. Now it works fine with the IN clause. Thanks