Creating IN Queries With Linq To Sql

An IN query will pull back a set of results from SQL that is within a given range. This range can be set manually, or can itself be a query. So if you have an eCommerce application and you want to know what products you have in a given user’s cart, you could do this

SELECT * FROM Production.Product WHERE ProductID IN (SELECT ProductID FROM Sales.ShoppingCartItem WHERE ShoppingCartID=’RobsCart’)

you could do this in LINQ like this:

var itemQuery = from cartItems in db.SalesOrderDetails
                select cartItems.ProductID;


var myProducts = from p in db.Products
                where itemQuery.Contains(p.ProductID)
                select p;

Here is the key to this weirdness:

Linq To Sql only constructs the query when the Enumerator is tripped.