Hi, LLBLGEN

I need a predicate which generates following sql:

select distinct u.* from [user] u where not exists ( select a.id from address a where a.userid = u.id and not (a.title like 'd%') )

actually - it used in order to only those master records for which all its details are conforms some predicate.

Currently playing with FieldCompareSetPredicate is not success

Could it be re-written as:

select distinct u.* from [user] u
where u.id IN
(
    select a.userid from 
    address a 
    where  not (a.title like 'd%')
)

If the answer is yes, then a FieldCompareSetPredicate can be used to execute the above query.

hi, Walaa

I think - not - because the result of query should be only record for which all child entities are passed some predicate (like 'd%'). Seems to be - I have done the query by following way:

        string alias = "alias";

        FieldLikePredicate originalPredicate = new FieldLikePredicate(AddressFields.Title, null, alias, "d%");

        EntityField2 field = AddressFields.UserId;
        field.ObjectAlias = alias;
        FieldCompareSetPredicate predicate = new FieldCompareSetPredicate(
            null, null,
            field, null,
            SetOperator.Exist,
            (field == UserFields.Id) & !originalPredicate,
            alias,
            true
            );

        IRelationPredicateBucket bucket = new RelationPredicateBucket();
        bucket.PredicateExpression.Add(predicate);

        EntityCollection<UserEntity> coll = new EntityCollection<UserEntity>(new UserEntityFactory());
        using(DataAccessAdapterBase adapter = new DataAccessAdapter())
        {
            adapter.FetchEntityCollection(coll, bucket);
        }

thanks for reply

Thanks for the feedback.