Parameterized IN clauses with ADO.NET and LINQ

Since my article on using parameters for Preventing SQL Injection In ASP.NET, I have had a few emails asking how to do the same with an IN clause expression. In this entry, I explore a couple of ways to do this.

Just in case you don't know, IN() takes a subquery or expression. The expression is a comma-separated list of values, so the following SQL:

SELECT * FROM Products WHERE CategoryID IN (1,4,8)

will return all Products that have a CategoryID of 1, or 4, or 8. It's a lot simpler to use than a series of OR statements such as

SELECT * FROM Products WHERE CategoryID = 1 OR CategoryID = 4 OR CategoryID = 8

Where it's particularly useful is if you allow the user to choose from a number of categories using CheckBoxes or similar. However, simply doing this:

SELECT * FROM Products WHERE CategoryID IN (@List)
Command.Parameters.AddWithValue("@List", values);

doesn't work, in that @List is a single value. SQL Server doesn't know it should parse whatever @List has as a value into a series of individual values, unless you tell it what to do. There are a large number of SQL Server-specific solutions for this, which are detailed and maintained by SQL Server MVP Erland Sommerskog here. But they all require that you have access to the SQL Server, and can create stored procedures etc. Oh, and that you are not using a database such as Access which doesn't support stored procedures containing control of flow code. What we really want is a solution that can be applied using purely application code.

Recapping the previous article on parameters, a parameterized query consists of a SQL statement (a string) and a collection of Parameter objects. Taking this into account, what we really need to do is construct something like the follow:

SELECT * FROM Products WHERE CategoryID IN (@p1, @p2, @p3)

Since an IN clause contains a variable number of values in its expression, it is impossible to know at design time how many parameters are needed, so these along with the SQL itself need to be constructed at runtime. And once you get your head around that, the rest should fall nicely into place.

I'm going to make this easy and illustrate how to do this with a series of CheckBoxes. Actually, this isn't a series of Checkboxes - the elements are raw HTML inputs of type checkbox:


<input type="checkbox" name="categories" value="1" />Beverages<br />
<input type="checkbox" name="categories" value="2" />Condiments<br />
<input type="checkbox" name="categories" value="3" />Confections<br />
<input type="checkbox" name="categories" value="4" />Dairy Products<br />
<input type="checkbox" name="categories" value="5" />Grains/Cereals<br />
<input type="checkbox" name="categories" value="6" />Meat/Poultry<br />
<input type="checkbox" name="categories" value="7" />Produce<br />
<input type="checkbox" name="categories" value="8" />Seafood<br />

The reason I have used HTML elements instead of CheckBoxes is that you can give all elements the same name attribute, and when they are posted to the server, all checked values are concatenated nicely into a comma delimited list. If you are binding a CheckBoxList server control, you will need to loop through the Items collection and build your own comma separated string. Moving on, in the Button_Click event of this page, the following code appears:


protected void Button1_Click(object sender, EventArgs e)
{
  var connect = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
  var sql = "SELECT * FROM Products WHERE CategoryID IN ({0}) ORDER BY CategoryID";
  var values = Request.Form["categories"];
  var valuearray = values.Split(',');

  var parms = valuearray.Select((s, i) => "@p" + i.ToString()).ToArray();
  var inclause = string.Join(",", parms);

  using (var conn = new SqlConnection(connect))
  {
    var cmd = new SqlCommand(string.Format(sql, inclause), conn);
    for (var i = 0; i < valuearray.Length; i++)
    {
      cmd.Parameters.AddWithValue(parms[i], valuearray[i]);
    }
    conn.Open();
    var reader = cmd.ExecuteReader();
    GridView1.DataSource = reader;
    GridView1.DataBind();
  }
}

All I have done here is to take the comma-separated string from the Request.Form collection, and then used string.Split() to turn it into an array. Using a touch of LINQ, I've then constructed another array with formatted values, so that the elements now take the shape of @p1, @p2 etc. string.Join() is then used to convert the new array into a comma separated string holding the new values, which is then added to the SQL.

Finally, once the Command object has been declared, the initial array is looped through so that the values themselves can be applied to the parameters that are added to the collection.

If you are using LINQ To SQL, or LINQ with the Entity Framework, the answer is much easier. You simpy use the Contains() extension method:


protected void Button1_Click(object sender, EventArgs e)
{
  var db = new NorthwindDataContext();
  var temp = Request.Form["categories"].Split(',');
  var output = Array.ConvertAll(temp, s => int.Parse(s));

  var query = db.Products
                  .Where(p => output.Contains(p.CategoryID))
                  .OrderBy(p => p.CategoryID);

  GridView1.DataSource = query;
  GridView1.DataBind();
}

The SQL this generates is just as you would expect:

exec sp_executesql N'SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], 
[t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], 
[t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
FROM [dbo].[Products] AS [t0]
WHERE ([t0].[CategoryID]) IN (@p0, @p1, @p2)
ORDER BY [t0].[CategoryID]',N'@p0 int,@p1 int,@p2 int',@p0=1,@p1=4,@p2=7

Woo hoo. You gotta love a bit of LINQ. Again, the Request.Form value is converted to an array of strings using the string.Split() method. To work with the type-safety of LINQ to SQL generated classes and their properties, this then needs to be converted to an array of ints. A nice little method Array.ConvertAll(TInput, TOutput) helps magnificently here. If you read the docs, you will see that it takes an array and a delegate pointing to the method that will perform the conversion from one type to the other. As soon as you see that a method takes a delegate as an argument, it's a candidate for a Lambda expression. This one is no different. The lambda takes each element of the string array and uses int.Parse() to convert it to an int.

One final thing to mention. If you try this yourself with the default schema of the Northwind database, you will probably get a complaint from the compiler around the Contains() method. The reason for this is that Products.CategoryID is by default a nullable int - arising from the fact that the default design of the Northwind database allows nulls in that particular column. The compiler is mightily satisfied if you explicitly cast p.CategoryID to an int:


.Where(p => output.Contains((int)p.CategoryID))

Or you can do what I did - amended the database column so that it does not allow nulls, single click CategoryID in the Products class in the LINQ to SQL designer, go to the properties panel and set Nullable to false.

Date Posted: Monday, September 28, 2009 9:53 PM
Last Updated: Friday, October 10, 2014 9:13 PM
Posted by: Mikesdotnetting
Total Views to date: 37536

11 Comments

Wednesday, October 14, 2009 2:52 AM - Naomi

Terrific article, thanks.

Monday, November 16, 2009 11:21 PM - Abishek Bellamkonda

How about

.Where(p => p.CategoryID.HasValue && output.Contains(p.CategoryID.Value))

Tuesday, November 17, 2009 6:32 PM - HoangDung, Le

Nice! Thanks!

Saturday, June 19, 2010 3:47 AM - Steve Wellens

Aside from your unnecessary use of the var keyword, it's a great article.

Your writing is sublime.

Saturday, June 19, 2010 8:32 AM - Mike

@Steve

I like using var where the return type is obvious and doens't need to be stated explicitly for code readbility reasons.

But thanks for your comments :o)

Friday, October 15, 2010 11:14 AM - lukas

hi,
i have quastion contains method is suported in framewrok version 3.5 ?

Saturday, October 16, 2010 8:31 AM - Mike

@lukas

Yes, it is.

Saturday, August 17, 2013 9:40 AM - Afzaal Ahmad Zeeshan

Hi Mike. Why is @p1 being used here? Shouldn't that be @0?

Thursday, August 29, 2013 10:03 AM - Mike

@Afzaal,

Not necessarily. It can be anything. The rule that specifies that parameter markers must be named incrementally @0, @1, @2 etc only applies to the Web Pages Database helper.

Wednesday, December 17, 2014 12:36 PM - Emily

Thanks, very helpful!!!!

Can i use this for multiple in's ?????

IN (.....) and IN(...) and IN (...)


Thursday, December 18, 2014 7:46 AM - Mike

@Emily

No, the helper won't cater for that. But your requirement doesn't make any sense to me either.
Add your comment

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and some may not be published. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Not relevant to the article
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam
  • Anything in a language I don't understand including gibberish.

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.

Recent Comments

Ingmar 1/31/2015 7:23 PM
In response to Scheduled Tasks In ASP.NET With Quartz.Net
On December, 1st, Mohammed reported a problem with triggers not being executed when the web is have...

Kiran More 1/29/2015 9:15 AM
In response to Nested Layout Pages with Razor
This article makes me easy understand of Nested Layout...

stephen 1/28/2015 9:53 PM
In response to ASP.NET MVC 5 with EF 6 - Working With Files
Hi, Sorry for wasting your time, I had a comment <!--Images--> inside the form this stopped it from...

stephen 1/28/2015 9:47 PM
In response to ASP.NET MVC 5 with EF 6 - Working With Files
Everything has been ok up until this part, here's a snippet of the entire file but i removed lines =...

Stephen kenny 1/28/2015 8:26 PM
In response to ASP.NET MVC 5 with EF 6 - Working With Files
Hi, great article. My issue is with the create view. @using (Html.BeginForm("Create", "Reptiles",...

Jenelyn 1/27/2015 6:44 PM
In response to MVC 5 with EF 6 in Visual Basic - Implementing Basic CRUD Functionality
I'm searching CRUD using VB.net 2010 and I can't find any, how sad...

Kadu Portugal 1/23/2015 10:55 AM
In response to Scheduled Tasks In ASP.NET With Quartz.Net
Hi Mike, Thank you for your useful article. I implement it in my project, and everything run...

Satyabrata 1/23/2015 6:02 AM
In response to Usage of the @ (at) sign in ASP.NET
Refreshed a lot of things.Thanks...

Vo Thanh Chinh Trung 1/22/2015 4:30 PM
In response to Adding A View
It's very helpful...Thank you so much, Mikes!...

Joop Stringer 1/22/2015 12:00 PM
In response to Inline Editing With The WebGrid
How to add a row for a new record ?...