Hello All,

Iam trying to formulate the below query using FieldCompareSetPredicate and im not reaching anywhere !! Any help with samples would be helpful !

Thanks

SELECT Per.Name,Per.Age,Addr.City,Addr.Zip FROM Person Per, Address Addr WHERE Per.Person_id =* Addr.Person_ID AND Per.Person_ID IN (SELECT P.Person_ID FROM Person P, Payment Pay WHERE P.Person_ID = Per.Person_ID AND Pay.Person_ID = P.Person_ID AND Pay.Type= "MNTH")

Please post a code snippet of what you have tried so far.

(Edit) A general tip: always investigate the generated SQL query to find out what's going wrong. (ref: manual's section "Using the generated code -> Troubleshooting and debugging")

I tried this

        PersonCollection PersonColl = new PersonCollection();

        IRelationCollection relationsToUse = new RelationCollection();
        relationsToUse.Add(PersonEntity.Relations.PersonAddressEntityUsingPersonId);

        IPredicateExpression subQueryFilter = new PredicateExpression();

        subQueryFilter.Add(
            new FieldCompareExpressionPredicate(
                PersonFields.PersonId.SetObjectAlias("person_inner"),
                ComparisonOperator.Equal, new Expression(PersonFields.PersonId)));

        subQueryFilter.Add(
            new FieldCompareExpressionPredicate(
                PaymentFields.PersonId,
                ComparisonOperator.Equal, new Expression(PersonFields.PersonId)))

        IPredicateExpression filter = new PredicateExpression();

        filter.AddWithAnd(
                new FieldCompareSetPredicate(
                        PersonFields.PersonId,
                        PersonFields.PersonId,
                        SetOperator.In,
                        subQueryFilter));

        PersonColl.GetMulti(filter, relationsToUse);

Please guide me to the right path,

Thanks

        PersonCollection PersonColl = new PersonCollection();

SELECT Per.Name,Per.Age,Addr.City,Addr.Zip

If you want to fetch fields from more than one table/entity then you should use a DynamicList.

Anyway:

        IRelationCollection relationsToUse = new RelationCollection();
        relationsToUse.Add(PersonEntity.Relations.PersonAddressEntityUsingPersonId);

You haven't specified the outer join, please look for overloads of the above Add() methods, to specify the join hint.

FROM Person Per, Address Addr

Are you joining to PersonAddress or to Address?

        IPredicateExpression subQueryFilter = new PredicateExpression();

        subQueryFilter.Add(
            new FieldCompareExpressionPredicate(
                PersonFields.PersonId.SetObjectAlias("person_inner"),
                ComparisonOperator.Equal, new Expression(PersonFields.PersonId)));

        subQueryFilter.Add(
            new FieldCompareExpressionPredicate(
                PaymentFields.PersonId,
                ComparisonOperator.Equal, new Expression(PersonFields.PersonId)))

        IPredicateExpression filter = new PredicateExpression();

        filter.AddWithAnd(
                new FieldCompareSetPredicate(
                        PersonFields.PersonId,
                        PersonFields.PersonId,
                        SetOperator.In,
                        subQueryFilter));

1- You didn't add a JOIN/Relation to Payment as You are filtering on it. 2- You should have used an alias in the FieldCOmapreSetPredicate as follows:

            filter.AddWithAnd(
                    new FieldCompareSetPredicate(
                            PersonFields.PersonId,
                            PersonFields.PersonId.SetObjectAlias("person_inner"),
                            SetOperator.In,
                            subQueryFilter));       

Thanks a lot ! I have one more question.

With the Lines

  subQueryFilter.Add(
            new FieldCompareExpressionPredicate(
                PaymentFields.PersonId,
                ComparisonOperator.Equal, new Expression(PersonFields.PersonId)))

I will get the SQL Payment.PersonId = Person.PersonId

There is one Scenario in which i have to use a Left outer join inside the Subquery. Can i still do that with the Comparison operator ?

For example

SELECT Per.Name,Per.Age,Addr.City,Addr.Zip FROM Person Per, Address Addr WHERE Per.Person_id =* Addr.Person_ID AND Per.Person_ID IN (SELECT P.Person_ID FROM Person P, Payment Pay WHERE P.Person_ID = Per.Person_ID AND Pay.Person_ID =* P.Person_ID AND Pay.Type= "MNTH")

Notice the Outer join is inside the Subquery !

Thanks

subQueryFilter.Add(
            new FieldCompareExpressionPredicate(
                PaymentFields.PersonId,
                ComparisonOperator.Equal, new Expression(PersonFields.PersonId)))

You shouldn't use the above code, rather you should use a relation/Join with a join hint for outer join just as you would do in the outer query.

I added the Relations for the Subquery as follows.

RelationCollection subqueryrelations = new RelationCollection(); subquery.relations.Add(PaymentEntity.Relations.PaymentEntityUsingPersonPersonID, JoinHint.Left);

But how can i add this to the main query's predicate ?

Thanks

The FieldCompareSetPredicate has an overload that accepts a RelationCollection, that's where you should add the subquery's outerjoin/relation. Since the FieldCompareSetPredicate is the one constructing the subquery.