What is the LINQ equivalent of SQL? Keyword "IN"

Question

How can I write below sql query in linq

select * from Product where ProductTypePartyID IN ( select Id from ProductTypeParty where PartyId = 34 ) 

Answer 1

Syntactic variations aside, you can write this in much the same way.

 from p in ctx.Product where (from ptp in ctx.ProductTypeParty where ptp.PartyId == 34 select ptp.Id).Contains(p.ProductTypePartyID) select p 

I prefer to use the existential quantifier, though:

 from p in ctx.Product where (from ptp in ctx.ProductTypeParty where ptp.PartyId == 34 && ptp.Id == p.ProductTypePartyID).Any() select p 

I expect this form to be allowed for EXISTS (SELECT * ...) in the generated SQL.

You will need to profile both options if there is a big difference in performance.

Answer 2

There is no direct equivalent in LINQ. Instead, you can use contains () or any other trick to implement them. Here is an example that uses Contains :

 String [] s = new String [5]; s [0] = "34"; s [1] = "12"; s [2] = "55"; s [3] = "4"; s [4] = "61"; var result = from d in context.TableName where s.Contains (d.fieldname) select d; 

check this link: in the Linq section

 int[] productList = new int[] { 1, 2, 3, 4 }; var myProducts = from p in db.Products where productList.Contains(p.ProductID) select p; 

Answer 3

Something like this

 var partyProducts = from p in dbo.Product join pt in dbo.ProductTypeParty on p.ProductTypePartyID equal pt.PartyId where pt.PartyId = 34 select p 

Answer 4

You are using the Contains clause in the Where application.

Something along these lines (unchecked):

 var results = Product.Where(product => ProductTypeParty .Where(ptp => ptp.PartyId == 34) .Select(ptp => ptp.Id) .Contains(product.Id) );