Sunday, 11 July 2010

Linq and Lambda equivalent of SQL Where in

Working with LINQ to SQL, several times I have come across a need of writing a LINQ query equivalent of T-SQL “Where IN” clause.

This is what T-SQL query looks like.

SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax

FROM Customers

WHERE (Country IN ('UK', 'USA', 'Australia'))

How do you write Where IN in LINQ?

Below is the query which can be used for a Linq “WHERE IN” scenario.

Query 1:

string[] countries = new string[] { "UK", "USA", "Australia" };

var customers = from c in context.Customers

where countries.Contains(c.Country)

select c;

Below is the query which can be used for a Lambda “WHERE IN” scenario.

Query 2

string[] countries = new string[] { "UK", "USA", "Australia" };

var customers = context.Customers.Where(c => countries.Contains(c.Country));