6/2/11

LINQ to SQL Using Where in

In SQL, you can compare against multiple values by using the IN operator. A normal select statement with the IN operator looks as follows:

select * from AuditLog
where log_id in (1111,2222,3333)
What if we need to use LINQ to SQL to accomplish the same? The approach is to use an array and use the Contains method to compare against the columns values. This is what the SQL statement listed above looks in LINQ:
int[] ids = { 1111, 2222, 3333 };

from log in dbcontext.AuditLog
where ids.Contains(log.log_id)
select log
We first declare an integer array with the values we are looking for. We then use the Contains method to compare the values. If you add a SQL trace, you will be able to see how this LINQ statement is translated into a SQL statement that uses the IN operator.
*Note:  dbcontext is the data context class that is created when a dbml file is added to the project. This is what generates the LINQ classes that are used for the database access.
I hope this is useful.

og-bit.com