CODEWITHSUNDEEP
linqsql-to-linq-conversion
Pavan
Pavan

Reputation: 533

SQL "IN" statement in Linq conversion

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.

Upvotes: 0

Views: 41

Answers (3)

Nathan
Nathan

Reputation: 6216

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
}

Upvotes: 0

SmartDev
SmartDev

Reputation: 2862

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().

Upvotes: 1

StuartLC
StuartLC

Reputation: 107267

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});

Upvotes: 1

Related Questions