Parameterized IN Clause with ADO.Net and LINQ

Now a days on Asp.Net forum, many newbie developers asked questions related to parametrized IN clause with ADO.Net. In this post, I am showing you the way to use it in both (ADO.Net & LINQ).

Just in case if you don't know how to use IN clause and what is IN clause, IN clause takes a sub-query or expression. The expression is a comma-separated list of values like:

SELECT * FROM Student WHERE StudentID IN (2, 5)

Above query will pick all students that have a StudentID of 2 or 5. Above query saves your multiple OR clauses in WHERE condition like:

SELECT * FROM Student WHERE StudentID = 2 OR StudentID = 5

If you allow the user to choose multiple options using CheckBoxes or similar. You probably doing like this:

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

But it doesn't work because 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 many SQL Server-specific solutions available for this, which are detailed and maintained by SQL Server MVP's. But they all require that you have access to the SQL Server to create stored procedures etc.  Or if you are not using a database such as Access which doesn't support stored procedures containing control of flow code. Then? What we really want is a solution that can be applied using purely application code.

A parametrized query consists of a SQL statement and a collection of Parameter objects. What we really need to do is construct something like the follow:

SELECT * FROM Student WHERE StudentID 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 using Checkboxes - the elements are raw HTML inputs of type checkbox:

<input type="checkbox" name="Student" value="1" />John<br />
<input type="checkbox" name="Student" value="2" />Martin<br />
<input type="checkbox" name="Student" value="3" />Smith<br />
<input type="checkbox" name="Student" value="4" />Micheal<br />
<input type="checkbox" name="Student" value="5" />Steave<br />
<input type="checkbox" name="Student" value="6" />Bevan<br />
<input type="checkbox" name="Student" value="7" />Andrew<br />
<input type="checkbox" name="Student" value="8" />James<br />
<input type="checkbox" name="Student" value="9" />Kallis<br />

Moving on, in the Button_Click event of this page, the following code appears:

protected void Button1_Click(object sender, EventArgs e)
{
  var sqlQuery = "SELECT * FROM Student WHERE StudentID IN ({0}) ORDER BY StudentID";
  var studentIDs = "1, 5, 9";
  var studentArray = studentIDs.Split(',');

  var parameters = studentArray.Select((s, i) => "@p" + i.ToString()).ToArray();
  var inClause = string.Join(",", parameters);

  using (var connection = 
            new SqlConnection(
              ConfigurationManager.ConnectionStrings["TestDB"].ConnectionStringt))
  {
    var command = new SqlCommand(string.Format(sqlQuery, inClause), connection);

    for (var i = 0; i < studentArray.Length; i++)
    {
       command.Parameters.AddWithValue(parameters[i], studentArray[i]);
    }

    connection.Open();
    var reader = command.ExecuteReader();
    GridView1.DataSource = reader;
    GridView1.DataBind();
  }
}

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 TestDBDataContext();
  var studentIDs = "1, 5, 9";
  var output = Array.ConvertAll(studentIDs, s => int.Parse(s));

  var query = db.Students
                  .Where(s => output.Contains(s.StudentID))
                  .OrderBy(s => s.StudentID);

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

If you encouter an issue from compiler around the Contains() method, it might be possible that Student.StudentID is by default a nullable int. The compiler is mightily satisfied if you explicitly cast s.StudentID to an int:

.Where(s => output.Contains((int)s.StudentID))

Hope you understand whole article very well. Stay tune! :)