Using LINQ to Perform "WHERE IN (Value1,Value2)" Queries

I recently needed to select a few products from a database where the product ID matched up with a list of IDs.  That's easy to do with a normal SQL statement since you can use the "WHERE IN (Value1,Value2)" clause to find what you need.  However, I wanted to do it with LINQ in this case. 

After doing a little research I discovered that executing a query like this using LINQ/Lambdas was actually really easy.  Here's what I ended up doing:

public static Product[] GetProducts(Guid[] prodIDs)
    return GetProducts().Where(p => prodIDs.Contains(p.ProductID)).ToArray<Product>();

The code uses the Contains() method to search for a specific ProductID within the collection which ends up creating a WHERE IN type query.  You can do the same thing with LINQ:

public static Product[] GetProducts(Guid[] prodIDs)
   return (from p in GetProducts()
           where prodIDs.Contains(p.ProductID)
           select p).ToArray<Product>();

A sample of what the SQL generated by this type of code looks like is shown next.  Notice the WHERE IN that's included in the statement.

exec sp_executesql N'SELECT [t0].[ProductID], [t0].[ProductName], [t0].[ProductNumber], [t0].[ProductVersion], [t0].[ProductCategoryID], [t0].[Price], [t0].[Discount],
[t0].[DiscountThreshold], [t0].[DetailsLinkText], [t0].[DetailsUrl], [t0].[DetailsHtml], [t0].[LicenseKey], [t0].[DateTimeStamp]
FROM [dbo].[tblProducts] AS [t0]
WHERE [t0].[ProductID] IN (@p0, @p1)',
N'@p0 uniqueidentifier,@p1 uniqueidentifier',

 Update:  Thanks to Julie Lerman I just learned that there's also a SqlMethods.Like() method call that can be used as well (I'll have to admit I hadn't heard about it until Julie mentioned it in a post).  Anders posted about it here.


Sponsored by:

comments powered by Disqus


  • Thanks Dan, but how can we search for multiple values. In your title you put "WHERE IN(Value1, Value2)" but in your example you are searching for one value only.

  • Fayez,

    It's searching for multiple values actually. I updated the post so that you could see what the SQL generally looks like and you'll see that two values are included in the WHERE IN statement since when I ran it the Guid array had two values in it. While it appears at first glance that one value is used, it's actually saying "search for a ProductID value in the Guid array". It's opposite of what you thought was happening....kind of weird until you get used to it.

Comments have been disabled for this content.