Use value of List inside IN() clause

Question

I have a List<int> whose values I want to use inside IN() clause in my query. I have read many similar post but none of them worked for me yet.

Let's say my table T is:

Id1 (int) | Id2(int)

Query:

Select Id1 
From T
where Id2 in (5,7,9,11)

List:

List<int> ls = new List<int>();

ls.add(5);
ls.add(7);
ls.add(9);
ls.add(11);

Now, how to populate this list in my query as no of integers?

Tried so far:

1)

string ls = string.Join(",", Id2.ToArray());

 string getId1 = "select Id1 from T where Id2 in (@ls)";
  cmd = new SqlCommand(getId1, con);
  cmd.Parameters.Add(new SqlParameter("@ls", ls));

Error:

Conversion failed when converting the nvarchar value '5,7,9,11' to data type int.

2)

 var ls = "("
       + String.Join(",", Id2.Select(x => x.ToString()).ToArray())
       + ")";

  string getId1 = "select Id1 from T where Id2 in (@ls)";
  cmd = new SqlCommand(getId1, con);
  cmd.Parameters.Add(new SqlParameter("@ls", ls));

Error:

Conversion failed when converting the nvarchar value '(5,7,9,11)' to data type int.

Note : I'll prefer the answer without using LINQ unless it's only possible with LINQ

You either have to concatenate them in or use multiple parameters. Or you can use a Table Valued parameter. — juharr, May 01 '18 at 12:44
@CodeCaster In my question, IN() is used for an int column which makes it different from the question you suggested as duplicate. Please unmark it as duplicate. — Harshil Doshi, May 01 '18 at 12:49
The type of the column doesn't matter the issue at hand is that `IN` expects multiple values and if you give it just one paramter that's one value. It does not magically separate comma delimited values for you. — juharr, May 01 '18 at 12:51
Although if you use Dapper it will handle creating the parameters and replacing your single parameter for you. — juharr, May 01 '18 at 12:58

score 2 · Accepted Answer · answered May 01 '18 at 12:52

2

    List<int> ls = new List<int>();
    ls.Add(5);
    ls.Add(7);
    ls.Add(9);
    ls.Add(11);
    string sql = string.Format( "select Id1 from T where Id2 in ({0})", string.Join(",",ls.Select(n=> "@prm"+n).ToArray()));
    SqlCommand cmd = new SqlCommand(sql);
    foreach(int n in ls){
        cmd.Parameters.AddWithValue("@prm"+n, n);
    }

answered May 01 '18 at 12:52

Murat Tüfekçi

93
5

it's not working when the values are string & having white spaces. Any work-around for this case?or any better approach is there? – Harshil Doshi Nov 23 '18 at 08:11

Use value of List inside IN() clause

1 Answers1