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:
