all posts

LINQ to SQL - How to Where in (value1,value2, ... valueN)

Published to Blog on 17 Jun 2008

According to FeedBurner the count of my blog subscribers has been steadily decreasing the last couple of weeks. I can't blame those quitters because my posts have been few and far between. But for those of you beautiful and intelligent people with enough patience to wait for something worthwhile - you're in luck today.

Several days ago I was working with a LINQ to SQL statement where I wanted to get an item if it's name existed in a string array of passed in values. I needed a LINQ translation for the SQL " where in (value1, value2, ... , valueN)" syntax. Intellisense provided little value. I had to turn to Google for answers. Do you realize how difficult it is to search for the keyword "in" on Google?!? It was nightmare. I think I got lucky when I tried "where in value". I found this post from Dan Wahlin that provided an example of exactly what I was trying to do:

http://weblogs.asp.net/dwahlin/archive/2008/05/09/using-linq-to-perform-quot-where-in-value1-value2-quot-queries.aspx

The solution is actually rather simple if you look at it from a programming perspective, but I have a tough time remembering that LINQ is not a SQL syntax, but is instead a first class part of the framework/language that is used for querying. Below is the example that Dan provided in his post:

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

Just like you can say "if (myobj == null)" and it means the same thing as "if (null == myobj)" the LINQ syntax is a bit clearer if you think about reversing the order of the equality. So instead of thinking about it as "if my object is in this bucket" think of it instead as "if this bucket contains my object". After seeing this it made sense to me. And LINQ and I became closer, maybe even friends.


Dan Hounshell
Web geek, nerd, amateur maker. Likes: apis, node, mobile, motorcycles, watches, food, Nashville, Savannah, Cincinnati and family. Dislikes: mean people
Dan Hounshell on Twitter


  • On 17 Jun 2008 Justin Kohnen said:

    Ah Ha.

    Makes sense. Thanks for the pointer Dan.

    Your loyal fan,

    Justin

  • On 17 Jun 2008 Dan Hounshell said:

    Thanks for staying, Justin. I had no doubt you were one of the loyal few!

  • On 17 Jun 2008 TrackBack said:
  • On 17 Jun 2008 jayson knight said:

    Just learned about this the other day myself...everything seems a little backwards in LINQ <-> SQL, but after a while it would seem that T-SQL actually has it all backwards. For me at least.

  • On 19 Jun 2008 TrackBack said:
  • On 10 Sep 2009 ferixxx said:

    thanks great job