Hi,

(didn't know which term to search on, so just send me a thread if you know it has been addressed before)

I have the following (just fictional as easy illustration):

A client has orders A order has articles Articles have parts parts have manufactured dates

How can I get a ClientCollection which have at least one part manufactured in the period x till y in a effiecient way?

Instead of getting als manufactured parts and then with some looping and large filters the articles, orders and finally clients.

thanks for any help,

Gab

Gab,

This should be pretty straightforward.

1. Create a RelationPredicateBucket
2. Create relations on the bucket from client => orders => articles => parts (make sure you use inner joins)
3. Add a predicate expression to the bucket on parts, using the criteria you need.
4. Pass the bucket into the FetchEntityCollection method (or the equivalent in Self-Servicing)

HTH,

Phil

Hi,

The problem is I still don't get how to have a filter on having at least one part in a particular period.

Could you provide an example? Is there one on the forum/help files? I couldn't find it yet,

Cheers, G

A client has orders A order has articles Articles have parts parts have manufactured dates

How can I get a ClientCollection which have at least one part manufactured in the period x till y in a effiecient way?

First let's think of the query you want to execute to return the needed results. I think it should be as follows:

SELECT * FROM Client
WHERE Id IN 
(SELECT ClientId FROM Order 
INNER JOIN Article ON...
INNER JOIN Part ON ...
WHERE Part.ManufacturedDate BETWEEN x AND y)

And you can implement the previous IN predicate using a FieldCompareSetPredicate

Don't forget to build the INNER JOINS through a relation collection passed to the FieldCompareSetPredicate.

Walaa wrote:

First let's think of the query you want to execute to return the needed results. I think it should be as follows:

SELECT * FROM Client
WHERE Id IN 
(SELECT ClientId FROM Order 
INNER JOIN Article ON...
INNER JOIN Part ON ...
WHERE Part.ManufacturedDate BETWEEN x AND y)

And you can implement the previous IN predicate using a FieldCompareSetPredicate

Don't forget to build the INNER JOINS through a relation collection passed to the FieldCompareSetPredicate.

You don't need the subquery (or the FieldCompareSetPredicate), unless I'm missing something? You should be able to do it with straight joins.

See the FieldBetweenPredicate to build the WHERE clause on the dates as pointed out above.

Thanks for all the input.

I resolved it without joins hints and a simple clause as stated by you all.

Gab