I am building a complicated predicate. Can you guys tell me if this is right way to build the exists predicate?

Here is the code.

private IPredicate SetPredicateOperatorForNumberOfOrdersPlaced(EntityField2 field, EntityCollection<FilterPredicateResponseIntEntity> predicateValue, FilterOperatorsEnum filterOperator) { ResultsetFields dtFields = new ResultsetFields(4); dtFields.DefineField(ClientsFields.ClientId, 0, "ClientId", "c"); dtFields.DefineField(field, 1, "OrderId", "o"); dtFields.DefineField(ClientsFields.MerchantId, 2, "ClientsMerchantId", "c"); dtFields.DefineField(OrdersFields.MerchantId, 3, "OrdersMerchantId", "o");

        IGroupByCollection groupBy = new GroupByCollection();
        groupBy.Add(dtFields["ClientId"]);
        EntityField2 aggregateField = dtFields["OrderId"].SetAggregateFunction(AggregateFunction.Count);
        groupBy.HavingClause = new PredicateExpression(SetPredicateOperator(aggregateField, predicateValue, null, filterOperator));

        IRelationCollection relations = new RelationCollection();
        relations.Add(this.SetCustomRelationWithMerchantId(ClientsEntity.Relations.OrdersEntityUsingClientId, (EntityField2)dtFields["ClientsMerchantId"], (EntityField2)dtFields["OrdersMerchantId"]), "c", "o", JoinHint.Left);

        return new PredicateExpression(
          new FieldCompareSetPredicate(
          ClientsFields.ClientId,
          null,
          dtFields["ClientId"],
          null,
          SetOperator.Exist,
          ((EntityField2)dtFields["ClientId"] == ClientsFields.ClientId),
          relations,
          string.Empty,
          0,
          null,
          false,
          groupBy));
    }

SQL select * from clients where clients.merchantId = 123 and exists (Select c.ClientID as ClientID From clients c left join orders o on c.ClientID = o.ClientID and c.merchantID = 123 and o.merchantID = 123 where c.clientId = clients.clientId Group BY c.clientID having (( Count(o.orderID) in (0))))

Thanks,

It seems ok. When you use Exists you can pass null to the first EntityField parameter of the FieldCompareSetPredicate as it is ignored at runtime. You can see the Generated SQL to compare it with your desire approximate sql code.