Hi All,

I've got a simple problem to which I can't seem to find out the logical and simple solution.

The following situation:

I've got Orders, Orders have PurchaseItems (A purchase item contains the information that has been sent to a manufacturer as purchase for this order). Each OrderItem contains a SupplierId, so I know which OrderItem has to be supplied by whom.

I now want a query which returns all Orders for which no PurchaseItem is available for a certain supplierId. (So I can track the orders that still have purchasing to do per supplier, a purchase tracking screen shows a list of all supplier for which purchasing is still required and under each supplier the orders are shown).

I tried this using joins, etc.. But the only thing I am succeeding in is getting the orders which DO have a purchase item for a certain supplier:

OrderCollection orders = new OrderCollection();

RelationCollection relations = new RelationCollection();
relations.Add(OrderEntity.Relations.OrderItemEntityUsingOrderId);
relations.Add(OrderEntity.Relations.PurchaseEntityUsingOrderId);

PredicateExpression orderFilter = new PredicateExpression();
// This line without the second predicate makes sure I return all orders relevant to the supplier
orderFilter.Add(OrderItemFields.SupplierId == supplierId);
// This lin returns all orders which also have bene purchased for this supplier
orderFilter.Add(PurchaseFields.SupplierId == supplierId); 

I thought about negating the (PurchaseFields.SupplierId == supplierId) part, but no succes.

Who can pull me out of the problem and give me helicopter view again

thanks,

Gab

Sorry but, I'm confused about your schema. Do you have Supplier, Order, OrderItem, PurchaseItem & Purchase? Would you please sketch out the relations between these entities? Also if you do know the SQL query that you want to execute, would you please post it?

I assume you want something like the following:

SELECT * FROM Orders
WHERE OrderID NOT IN (SELECT OrderID FROM OrderDetail WHERE SupplierId = x)

If the above looks like the query you want, then you need to use a FieldCompareSetPredicate to implement it.

Hi,

Thanks for the response.

I just woke up this morning and thought of the approach of using filtered prefetch paths on PurchaseItems and loop through the Orders and remove the ones with a PurchaseItem.count >0. It works, but ain't beautiful.

Relation's are like this: Order -> OrderItems (Containing a SupplierId & OrderId (and more ) Order -> PurchaseItems (Containing a SupplierId & OrderId (and more )

Since we are implementing this in phases, in the current situation it's enough to have a supplier in the purchase items (later we need to do this per OrderItem). So each supplier used for a certain order needd to have 1 purchase item. So if we have 2 OrderItems for Supplier X and 5 for Supplier Y, we only need 2 PurchaseItems for the order, 1 for Supplier X and 1 for Supplier Y.

Indeed I want to see all suppliers which are used in an order, I am not an SQL guru (... LLBLGen has made me skip that step and forget the little knowledge I had... ) but guess indeed your query is the one I want.

Thanks for the input, i will work from here and guess I will find more resources on FieldCompareSetPredicate in the documententation and on the forum.

I just needed that little hint,

Thanks!

Gab