Databases
StackOverflow2013
Postsdbo
POLinq to Sql with the And operator on the same field
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLinq to Sql with the And operator on the same field
    text
    copied!<p>I have the following linq query (applied to the Northwind database)</p> <pre><code>(from od in OrderDetails where od.ProductID == 11 || od.ProductID == 42 select od.OrderID).Distinct() </code></pre> <p>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 <strong>includes both product 11 and 42</strong>? The resulting list should only feature one order (orderid = 10248)</p> <p>Obviously the following query does not return any orders.</p> <pre><code>(from od in OrderDetails where od.ProductID == 11 &amp;&amp; od.ProductID == 42 select od.OrderID).Distinct() </code></pre> <p>Here is a sql query that does the job but what is the best (or most efficient) way of writing it in linq?</p> <pre><code> 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))) </code></pre> <p>[edit]</p> <p>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.</p> <pre><code>public static Expression&lt;Func&lt;Order, bool&gt;&gt; WhereProductIdListEqualsAnd( int[] productIds ) { var condition = PredicateBuilder.True&lt;Order&gt;(); foreach ( var id in productIds ) { condition = condition.And( o =&gt; o.OrderDetails.Any( od =&gt; od.ProductId == id ) ); } return condition; } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload