Working with LINQ to SQL I came 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 I write Where IN in LINQ?

Below is the query which can be used for a “WHERE IN” scenario. Query 1 uses query syntax and Query 2 uses lambda expression. I am using using a DataContext for Northwind database. See LINQ To SQL Tutorial to see how to generate a DataContext.

LINQ Query 1
string[] countries = new string[] { "UK", "USA", "Australia" };
var customers =
    from c in context.Customers
    where countries.Contains(c.Country)
    select c;
LINQ Query 2
string[] countries = new string[] { "UK", "USA", "Australia" };
var customers =
    context.Customers
    .Where(c => countries.Contains(c.Country));
Technorati Tags:

Tagged with:
 

8 Responses to LINQ Equivalent Of Where IN

  1. curious says:

    couldn’t I write it that way?

    var customers = from c in context.Customers
    where (c.Country == “UK” || c.Country == “USA” ||
    c.Country == “Australia”)
    select c;

  2. Deepak says:

    Yes that will also work.
    The solution I posted will also work when the elements of the array are unknown. Imagine this array being generated dynamically.

  3. LAD says:

    ArrayList is not supported

  4. Steve says:

    This only works with strings ?

  5. [...] I wrote a post showing how to write a LINQ query which gets transformed into a T-SQL query with a "Where In" clause. I had a comment on the post by Steve saying that the approach only works with strings. In this [...]

  6. Deepak says:

    Hi Steve,

    Here is a post I wrote which shows you how to do this with integers. I hope you will find it useful.

    http://www.thereforesystems.com/linq-equivalent-of-where-in-with-int/

  7. Lucia says:

    Thank you, Deepak, very useful.

  8. Deepak says:

    Lucia,

    Thank you for the kind words.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>