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',
@p0='D313761A-2500-431A-A7C6-244DC0611C64',@p1='32535049-5BBB-429B-9D45-38F689C4AED4'

 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: