LINQ to SQL: SQL IN clause

Here is a problem I recently had to figure out with LINQ: how do you do an IN with LINQ for SQL?

In standard SQL your query would look like this:

SELECT myColumn FROM myTable
WHERE myColumn IN (myVal1, myVal2, myVal3)

But lets put another kink in, shall we. I'm not using LINQ, I'm using Lambda.

It turns out my savior was Contains.

Every generic List ( List ) with System.Linq available has a Contains< > extension method (this is .Net 3.5 we are talking about here), and that is what you use.

So, when creating a new query via LINQ for SQL in Lambda, you get a IQueryable interface with a Where extension method ...

Oh crap, here is the code, this will take to long to fully explain:

MyDataDataContext cx = new MyDataDataContext(); // from the LINQ to SQL dbml
IQueryable q = cx.MyTables.AsQueryable(); // creates the query object

List listOfData = {1,2,3,4};

q.Where( x => listOfData.Contains(x.MyIntValue));

var result = q2.Select(x => x.MyIntValue);

So what have we, and what was created. Well, we just used LINQ to SQL to generate a query that will look like this:

SELECT [t0].[MyIntValue]
FROM [dbo.MyTable] AS [t0]
WHERE [t0.][MyIntValue] in ( @p1, @p2, @p3, @p4 )

There is a @p[number] for every value in the list 'listOfData' above. And to me, the generated SQL is pretty good. That is probably what I would write.