Linq to Sql with the And operator on the same field

Question

I have the following linq query (applied to the Northwind database)

(from od in OrderDetails
where od.ProductID == 11 || od.ProductID == 42
select od.OrderID).Distinct()

Which gives me a list of Order Ids (67 items) where the order includes either product 11 or 42. How can I rewrite the query to give me a list of Order Ids where the order includes both product 11 and 42? The resulting list should only feature one order (orderid = 10248)

Obviously the following query does not return any orders.

(from od in OrderDetails
    where od.ProductID == 11 && od.ProductID == 42
    select od.OrderID).Distinct()

Here is a sql query that does the job but what is the best (or most efficient) way of writing it in linq?

    SELECT DISTINCT OrderID
    FROM         [Order Details]
    WHERE     (OrderID IN
                              (SELECT     OrderID
                                FROM          [Order Details] AS OD1
                                WHERE      (ProductID = 11))) AND (OrderID IN
                              (SELECT     OrderID
                                FROM          [Order Details] AS OD2
                                WHERE      (ProductID = 42)))

[edit]

Thanks to klausbyskov for his solution. From that i was able to build an expression (using PredicateBuilder) that can take a dynamic list of product ids, use them in the where clause and return a list of orders. Here it is if anyone is interested.

public static Expression<Func<Order, bool>> WhereProductIdListEqualsAnd( int[] productIds )
{
    var condition = PredicateBuilder.True<Order>();

    foreach ( var id in productIds )
    {
        condition = condition.And( o => o.OrderDetails.Any( od => od.ProductId == id ) );
    }

    return condition;
}

Answer 1

You could simplify this into one query of course, but this would work:

var o1 = OrderDetails.Where( od => od.ProductID == 11).Select( od => od.OrderID );
var o2 = OrderDetails.Where( od => od.ProductID == 42).Select( od => od.OrderID );
var intersection = o1.Intersect(o2);

Another (possibly more efficient) way of doing it would be via a join:

(from o1 in OrderDetails
join o2 in OrderDetails on o1.OrderID equals o2.OrderID
where o1.ProductID == 11 and o2.ProductID == 42
select o1.OrderID).Distinct()

Answer 2

Start the query on the order relation instead:

var result = Orders.Where(o => o.OrderDetails.Any(od => od.ProductId == 11) 
                            && o.OrderDetails.Any(od => od.ProductId == 42));