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: 35699

9 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.
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

Gautam 11/20/2014 8:01 AM
In response to I'm Writing A Book On WebMatrix
Hello Mike, I read your book, loved it! However, I have a few request/suggestions: 1) an example...

Bret Dev 11/19/2014 8:39 PM
In response to The Difference Between @Helpers and @Functions In WebMatrix
Excellent post! One concern - where can you place global @Functions code within an MVC project to Is...

Rob Farquharson 11/19/2014 4:28 PM
In response to iTextSharp - Links and Bookmarks
How can I place text at an absolute position on the page? Also, how can I rotate text?...

Andy 11/17/2014 8:08 PM
In response to MVC 5 with EF 6 in Visual Basic - Sorting, Filtering and Paging
Hello I'm testing your sorting instructions above. This is great and I was able to get it to work...

Gautam 11/17/2014 5:51 PM
In response to WebMatrix - Database Helpers for IN Clauses
Hi Mike, I am very new to programming: In the above example if I want to use a delete button the...

donramon 11/17/2014 3:22 PM
In response to Entity Framework 6 Recipe - Alphabetical Paging In ASP.NET MVC
Congratulations on your new website look and the excellent articles. Thank you!...

Gautam 11/17/2014 11:26 AM
In response to Looking At The WebMatrix WebGrid
Hi Mike, I add the jquery script at the end of my html file.. when ajax attribute is added to the be...

Chet Ripley 11/15/2014 6:57 PM
In response to Adding A New Field
It appears the command is case sensitive. I had the same issue as Cameron. When I changed the to it...

Alvin 11/14/2014 12:49 PM
In response to Razor Web Pages E-Commerce - Adding A Shopping Cart To The Bakery Template Site
Great article Mike! When do you plan to extend the bakery shopping cart beyond this point?...

Gautam 11/14/2014 10:16 AM
In response to Web Pages - Efficient Paging Without The WebGrid
to get the count can we use only the below sql, why to join category and author table var sql =...