I am trying to use RelationCollection in my Typed List and I am trying to follow the example given in "Advanced Filtering" topic as

// C#
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.RelationCollection.Add(CustomerEntity.Relations.AddressEntityUsingVisitingAddressID, "VisitingAddress");
bucket.RelationCollection.Add(CustomerEntity.Relations.AddressEntityUsingBillingAddressID, "BillingAddress");
bucket.PredicateExpression.Add(PredicateFactory.CompareValue(
    AddressFieldIndex.City, 
    ComparisonOperator.Equal,
    "Amsterdam",
    "VisitingAddress"));
bucket.PredicateExpression.AddWithAnd(PredicateFactory.CompareValue(
    AddressFieldIndex.City, 
    ComparisonOperator.Equal,
    "Rotterdam",
    "BillingAddress"));
EntityCollection customers = new EntityCollection(new CustomerEntityFactory());
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntityCollection(customers, bucket);

but I don't get a RelationCollection as a part of IRelationPredicateBucket, but I get only Relations though!, What am I missing here?

Following works...

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(CustomerEntity.Relations.AddressEntityUsingVisitingAddressID, "VisitingAddress");

but the following doesn't....

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.RelationCollection.Add(CustomerEntity.Relations.AddressEntityUsingVisitingAddressID, "VisitingAddress");

Any Clues?

Ganesh wrote:

I am trying to use RelationCollection in my Typed List and I am trying to follow the example given in "Advanced Filtering" topic as

// C#
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.RelationCollection.Add(CustomerEntity.Relations.AddressEntityUsingVisitingAddressID, "VisitingAddress");
bucket.RelationCollection.Add(CustomerEntity.Relations.AddressEntityUsingBillingAddressID, "BillingAddress");
bucket.PredicateExpression.Add(PredicateFactory.CompareValue(
    AddressFieldIndex.City, 
    ComparisonOperator.Equal,
    "Amsterdam",
    "VisitingAddress"));
bucket.PredicateExpression.AddWithAnd(PredicateFactory.CompareValue(
    AddressFieldIndex.City, 
    ComparisonOperator.Equal,
    "Rotterdam",
    "BillingAddress"));
EntityCollection customers = new EntityCollection(new CustomerEntityFactory());
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntityCollection(customers, bucket);

but I don't get a RelationCollection as a part of IRelationPredicateBucket, but I get only Relations though!, What am I missing here?

Following works...

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(CustomerEntity.Relations.AddressEntityUsingVisitingAddressID, "VisitingAddress");

but the following doesn't....

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.RelationCollection.Add(CustomerEntity.Relations.AddressEntityUsingVisitingAddressID, "VisitingAddress");

Any Clues?

I believe that's a typo as "RelationCollection" is the type for "IRelationPredicateBucket.Relations".

Jeff...

If that be the case then can any one suggest me what am I doing wrong to get the following message on the following code...

Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?

IDataAccessAdapter adapter = DataAccessAdapterFactory.GetDataAdapter();
            ResultsetFields fields = new ResultsetFields(21);
            fields.DefineField(NameFormatViewFieldIndex.FirstMiddleLastSuf, 0, "StudentName","NameFormatViewStudent");
            fields.DefineField(RoomAssignFieldIndex.BldgLocCde, 1, "RoomAssignLocation");
            fields.DefineField(RoomAssignFieldIndex.BldgCde, 2, "RoomAssignBuilding");
            fields.DefineField(LocationMasterFieldIndex.LocDesc, 3, "Location");
            fields.DefineField(BuildingMasterFieldIndex.BuildingDesc, 4, "Building");
            fields.DefineField(RoomAssignFieldIndex.RoomCde, 5, "Room","RoomAssignment");
            fields.DefineField(RoomAssignFieldIndex.RoomType, 6, "RoomType","RoomAssignment");
            fields.DefineField(StudSessAssignFieldIndex.CampusBoxNum, 7, "CampusBoxNum","StudentSessAssign");
            fields.DefineField(AddressMasterFieldIndex.AddrCde, 8, "MailingAddressCode","AddressMasterStudent");
            fields.DefineField(AddressMasterFieldIndex.AddrLine1, 9, "AddressLine1","AddressMasterStudent");
            fields.DefineField(AddressMasterFieldIndex.AddrLine2, 10, "AddressLine2","AddressMasterStudent");
            fields.DefineField(AddressMasterFieldIndex.AddrLine3, 11, "AddressLine3","AddressMasterStudent");
            fields.DefineField(AddressMasterFieldIndex.City, 12, "City","AddressMasterStudent");
            fields.DefineField(AddressMasterFieldIndex.State, 13, "State","AddressMasterStudent");
            fields.DefineField(AddressMasterFieldIndex.Zip, 14, "Zip","AddressMasterStudent");
            fields.DefineField(AddressMasterFieldIndex.Phone, 15, "Phone","AddressMasterStudent");
            fields.DefineField(StudRoommatesFieldIndex.RoommateId, 16, "RoommateID");
            fields.DefineField(NameFormatViewFieldIndex.FirstMiddleLastSuf, 17, "RoommateName","NameFormatViewRoommate");
            fields.DefineField(AddressMasterFieldIndex.AddrCde, 18, "EmailAddressCode","AddressMasterRoommate");
            fields.DefineField(AddressMasterFieldIndex.AddrLine1, 19, "RoommateEmail","AddressMasterRoommate");
            fields.DefineField(CmSessionMstrFieldIndex.SessDesc, 20, "SessionDescription");

            IRelationPredicateBucket bucket = new RelationPredicateBucket();

            // define custom relation self-joined
            IEntityRelation CustomRelationA = new EntityRelation(RelationType.OneToOne);
            CustomRelationA.AddEntityFieldPair(EntityFieldFactory.Create(StudSessAssignFieldIndex.IdNum),
                EntityFieldFactory.Create(NameFormatViewFieldIndex.IdNum));

            // add this as a relation
            bucket.Relations.Add(CustomRelationA,"StudentSessAssign","NameFormatViewStudent",JoinHint.Inner);

            bucket.Relations.Add(RoomAssignEntity.Relations.StudSessAssignEntityUsingSessCdeIdNum,"RoomAssignment","StudentSessAssign", JoinHint.Left);
            bucket.Relations.Add(LocationMasterEntity.Relations.RoomAssignEntityUsingBldgLocCde, JoinHint.Left);
            bucket.Relations.Add(BuildingMasterEntity.Relations.RoomAssignEntityUsingBldgCde, JoinHint.Left);

            // define custom relation left-outer-joined
            IEntityRelation CustomRelationB = new EntityRelation(RelationType.OneToMany);
            CustomRelationB.AddEntityFieldPair(EntityFieldFactory.Create(StudSessAssignFieldIndex.IdNum),
                EntityFieldFactory.Create(AddressMasterFieldIndex.IdNum));

            // add this as a relation
            bucket.Relations.Add(CustomRelationB,"AddressMasterStudent",JoinHint.Left);

            // StudSessAssign left outer Joins with Stud_Roommates
            bucket.Relations.Add(StudSessAssignEntity.Relations.StudRoommatesEntityUsingSessCdeIdNum, JoinHint.Left);

            // define custom relation Left-Outer-joined
            IEntityRelation CustomRelationC = new EntityRelation(RelationType.OneToMany);
            CustomRelationC.AddEntityFieldPair(EntityFieldFactory.Create(StudRoommatesFieldIndex.RoommateId),
                                               EntityFieldFactory.Create(NameFormatViewFieldIndex.IdNum));

            // add this as a relation
            bucket.Relations.Add(CustomRelationC,"NameFormatViewRoommate",JoinHint.Left);

            // define custom relation left-outer-joined
            IEntityRelation CustomRelationD = new EntityRelation(RelationType.OneToMany);
            CustomRelationD.AddEntityFieldPair(EntityFieldFactory.Create(StudRoommatesFieldIndex.RoommateId),
                                               EntityFieldFactory.Create(AddressMasterFieldIndex.IdNum));

            // add this as a relation
            bucket.Relations.Add(CustomRelationD,"AddressMasterRoommate",JoinHint.Left);

            // cm_session_mstr joins with stud_sess_assign
            bucket.Relations.Add(StudSessAssignEntity.Relations.CmSessionMstrEntityUsingSessCde, JoinHint.Inner);

            // WHERE class
            IPredicateExpression A = new PredicateExpression();

            A.Add(PredicateFactory.CompareValue(StudRoommatesFieldIndex.ReqActualFlag, ComparisonOperator.Equal, "A"));
            A.AddWithOr(new FieldCompareNullPredicate(EntityFieldFactory.Create(StudRoommatesFieldIndex.RoommateId),null,false));

            //Add where criteria to the bucket
            bucket.PredicateExpression.Add(A);          

            IPredicateExpression B = new PredicateExpression();

            B.Add(PredicateFactory.CompareValue(AddressMasterFieldIndex.AddrCde, ComparisonOperator.Equal, strResidenceLifeMailingAddressor,"AddressMasterStudent"));
            B.AddWithOr(new FieldCompareNullPredicate(EntityFieldFactory.Create(AddressMasterFieldIndex.AddrCde),null,"AddressMasterStudent"));

            bucket.PredicateExpression.AddWithAnd(B);

            IPredicateExpression C = new PredicateExpression();

            C.Add(PredicateFactory.CompareValue(AddressMasterFieldIndex.AddrCde, ComparisonOperator.Equal, strResidenceLifeEmailAddressor,"AddressMasterRoommate"));
            C.AddWithOr(new FieldCompareNullPredicate(EntityFieldFactory.Create(AddressMasterFieldIndex.AddrCde),null,"AddressMasterRoommate"));

            bucket.PredicateExpression.AddWithAnd(C);

            IPredicateExpression D = new PredicateExpression();

            D.Add(PredicateFactory.CompareValue(StudSessAssignFieldIndex.IdNum, ComparisonOperator.Equal, idNum));

            bucket.PredicateExpression.AddWithAnd(D);

            // Add the "ORDER BY" clause cm_session_mstr.sess_end_dte desc
            ISortExpression sorter = new SortExpression();
            sorter.Add(SortClauseFactory.Create(CmSessionMstrFieldIndex.SessEndDte, SortOperator.Descending));

            DataTable dynamicList = new DataTable();
            adapter.FetchTypedList(fields, dynamicList,bucket, 0, sorter, true, null);

When you get that error it's typically caused by this: say you have 2 relations: A-B and B-C. When you add A.Relations.Busing.. and then B.Relations.Cusing..., this will result in: A inner join B B inner join C

As 'B' is already in the join list, this will thus result in the correct SQL: a inner join B on ... inner join C on ...

Though when you instead of B-C add the relation D-C, this will result in: A inner join B D inner join C

Though, now this won't resolve in a normal SQL join, as D isn't in the joinlist already and neither is C.

Often this is caused by the fact that hte order in which the relations are added isn't correct.

Though looking at your relations, at position 1 is a relation which has an alias which should be matching the relation already added...

However! the relation you add as the 3rd relation, has 2 entities which don't match with any previously added entities in the relations as all previous entities are aliased!

Once I removed all the aliases but for except the one which is twice joined tables, it worked!!!. But I feel I struggled a lot to get it done, reason may be I am not understanding well enough to order it, but the problem is it works fine on the SQL Query Analyser, OR the exception could give more info could have helped me to figured it out soon. Anyway thanks for your input on this.

Rule of thumb: don't bother with aliases unless you join an entity multiple times. In THAT case, alias every occurance of that entity. I think that will both simplify your code and also make you avoid aliassing errors in the future.

Thats what exactly I did and it worked. Thanks.