I have an object relationship like this: Collateral >> Customer >> Collaterals

So in the database, a collateral can have 1 customer, but a customer can have many collaterals.

In my code, I'm trying to load the data for a particular collateral, its customer, and the customer's other collaterals using prefetch. Of course the parent collateral object would normally be in the Customer's other collaterals. I want to filter this collateral OUT of the list, and leave the rest. I try to do it like this:

Dim coll As New CollateralEntity(ObjectID)
Dim prefetchPath As IPrefetchPath2 = New PrefetchPath2(CType(EntityType.CollateralEntity, Integer))
Dim customerPrefetch As IPrefetchPathElement2 = CollateralEntity.PrefetchPathCustomer

Dim bucket As New RelationPredicateBucket
bucket.PredicateExpression.Add(PredicateFactory.CompareValue(CollateralFieldIndex.CollateralId, ComparisonOperator.NotEqual, coll.CollateralId))

customerPrefetch.SubPath.Add(CustomerEntity.PrefetchPathCollateralCollection, -1, bucket.PredicateExpression)
customerPrefetch.SubPath.Add(CustomerEntity.PrefetchPathLocationCollectionViaCustomerLocation)
prefetchPath.Add(customerPrefetch)
adapter.FetchEntity(coll, prefetchPath)

The problem is that this code does not filter out the root collateral from the customer's collateral collection. However, when I change the ComparisonOperator to ComparisonOperator.Equal, it works as expected, only returning the root collateral in the collection.

(CollateralID is a GUID in case that matters)

Thoughts? Thank you.

If you inspect the queries generated, you will find them correct.

So what happens is, when you the fetch procedure adds the CustomerEntity to the fetched CollateralEntity related field, it automatically adds this CollateralEntity to the CustomerEntity Collaterals collection related field.

This is by design.

As when you do: myOrder.Customer = myCustomer; it also does: myCustomer.Orders.Add(myOrder);

The first assignment assigns the two together which also implies the other assignment.

If you don't want the CollateralEntity selected to appear in the inner collection, fetch the Collaterals collection in a new entitycollection.

That's about the conclusion I'd come to. Actually a very cool feature I guess. Your workaround will work fine for me. Thank you.

Walaa wrote:

If you inspect the queries generated, you will find them correct.

So what happens is, when you the fetch procedure adds the CustomerEntity to the fetched CollateralEntity related field, it automatically adds this CollateralEntity to the CustomerEntity Collaterals collection related field.

This is by design.

As when you do: myOrder.Customer = myCustomer; it also does: myCustomer.Orders.Add(myOrder);

The first assignment assigns the two together which also implies the other assignment.

If you don't want the CollateralEntity selected to appear in the inner collection, fetch the Collaterals collection in a new entitycollection.