I'm struggling to achieve what I thought would be real easy: retrieve a collection of all empty orders. That is, those orders that have no order lines.

In SQL I have the following:

SELECT * FROM [Order]
WHERE [Order].OrderId IN (
SELECT   [Order].OrderId
FROM         [Order] LEFT OUTER JOIN
                      OrderLine ON [Order].OrderId = OrderLine.OrderId
GROUP BY [Order].OrderId
HAVING    (COUNT(OrderLine.OrderLineId) = 0))

Not sure this is the most optimum way of doing this but I get a list of orders which don't contain order lines as a subquery and then get the orders themselves from the given list of orderIds.

I can construct the main query using predicates, probably using FieldCompareSetPredicate. But I don't know how to obtain a list of ints from the subquery to feed to this predicate.

Any suggestions?

Thanks.

retrieve a collection of all empty orders. That is, those orders that have no order lines

I think the query could be far more simpler as follows:

SELECT * FROM Orders
WHERE orderID NOT IN (SELECT orderId FROM OrderLine )

Thanks Walaa,

Using your query I constructed some LINQ to retrieve the empty order entities:

using (var adapter = GetDataAccessAdapter())
{
    LinqMetaData metaData = new LinqMetaData(adapter);
    var emptyOrders = from o in metaData.Order
                      where !(from ol in metaData.OrderLine
                              select ol.OrderId).Contains(o.OrderId)
                      select o;
}

Regards,