LINQ - In in LINQ - Asked By Shan on 23-May-11 03:36 AM

Hi,

How to use In in Linq
where columnname in('1','2') how to use this in LINQ

Ravi S replied to Shan on 23-May-11 04:26 AM

HI

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.

refer this example:

Start with the Cart (pretend my cart is ID=75144):

AdventureWorks.DB db=new DB();
var itemQuery = from cartItems in db.SalesOrderDetails
              where cartItems.SalesOrderID == 75144
              select cartItems.ProductID;

Next we need to get the products, but only those that are in the cart. We do this by using our first query, inside the second:

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

refer the links
http://blog.wekeroad.com/2008/02/27/creating-in-queries-with-linq-to-sql
http://solidcoding.blogspot.com/2007/12/sql-in-clause-in-linq.html

Riley K replied to Shan on 23-May-11 04:35 AM

Use Contains Method of LINQ

Let’s say that we want to select rows from Products table in Northwind database where ProductId matches 3, 7, 8, 10. Our query should include the Where IN clause. Something like this:

SELECT *
FROM Products
WHERE ProductID in (3, 7, 8, 10)

Of course Select * is not a good way to write any Query but this is just to convey a point. LINQ query which will work with integers and give me a Where IN looks like this:

List<int> productIds = new List<int> { 3, 7, 8, 10 };
using (NorthwindDataContext context = new NorthwindDataContext())
{
  var query = from p in context.Products
    where productIds.Contains(p.ProductID)
    select p;
}