SQL "IN" statement in Linq conversion

Question

I have a SQL Query and I wanted to convert into LINQ can you please help to do this.

SELECT p.ProductID, p.ProductName FROM tblProducts p
WHERE p.ProductID IN (SELECT s.ProductID FROM tblStock s WHERE s.BarCode = 2222)

In this case BarCode is a null able type.

Answer 1

Use Contains to translate to 'IN':

tblProducts.Where(p => tblStock.Where(ts => ts.BarCode == 2222)
                               .Select(ts => ts.ProductId)
                               .Contains(p.ProductID))
           .Select(p => new {p.ProductID, p.ProductName});

Answer 2

You should join instead of in:

var result = tblProducts
.Join 
(
tblStock.Where(s => s.BarCode = 2222),
p => p.ProductID,
s => s.ProductID,
(p, s) => new { ProductID = p.ProductID, ProductName = p.ProductName }
);

Depending on your data and needs, you could also add .Distinct().

Answer 3

This should be written as an inner join for efficiency. In Linq:

var myQuery = from p in myContext.tblProducts
                 join s in myContext.tblStock on p.ProductID equals s.ProductID
                    where s.BarCode = 2222
                       select new { ProductID = p.ProductID, ProductName = p.ProductName };

foreach(var product in myQuery)
{
   // do stuff
}