Hi experts,

I just face another problem: Imagine you have customers which are related to orders in a 1:n relationship (one customer can have multiple orders).

Let's say you already have a CustomerCollection "LotsOfCustomers".

What would be the best way to get an OrdersCollection "AllTheirOrders" that contains all orders for the customers given in "LotsOfCustomers"?

Is there a better approach than iterating through all customers like this this (pseudo-code)?

for each (CustomerEntity currentCustomer in LotsOfCustomers)
  {
      for each (OrdersEntity currentOrder in currentCustomers.Orders)
      { 
          AllTheirOrders.Add(currentOrder);
      }
   }

Thanks for any help! Ingmar

I would simply initialise the collection of orders with a FieldCompareRangePredicate which specifies all customers obtained from the customers collection

Hi Ingmar, You can use FieldCompareRangePredicate, so only one hit to DB is performed.

System.Collections.ArrayList values = new System.Collections.ArrayList();
foreach (CustomerEntity c in LotsOfCustomers)
    values.Add(customer.CustomerId);

OrderCollection AllTheirOrders = new OrerCollection();
AllTheirOrders.GetMulti(OrderFields.CustomerId == values);

I'm afraid there's no an easy way where you avoid iteration.

Hi Matt & David,

thanks for your suggestions. Unfortunately the list of customers can be extremely big, 10.000 and more. The following command won't work due to a certain restriction of MS SQL.

AllTheirOrders.GetMulti(OrderFields.CustomerId == values);

Anyway, you already helped me a lot by telling me that there is no easy way. Thanks again! Ingmar

Another way is if you could formulate the query to "Retrieve all orders from lost customers".

SELECT * FROM orders 
WHERE CustomerID IN
     (SELECT CustomerID FROM Customer WHERE <condition>

Then you could formulate a predicate in one go, using a FieldCompareSetPredicate.