WHERE with IN Clause - C#

Question

I need to do an IN clause in my ID (INT)

But when I do this:

  strQuery = "SELECT something FROM some table \n";
  strQuery += "WHERE ID IN ({0})";

  var idParameterList = new List<string>();

  foreach (var id in lstLigID)
  {
      idParameterList.Add(id.ToString());
  }

  dbContext.Database.ExecuteSqlCommand(strQuery, string.Join(",", idParameterList));

I get this error:

or read this carefully https://learn.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql?view=sql-server-2017 — TAHA SULTAN TEMURI, Feb 07 '19 at 18:48
First - there's a comma after the 22. SQL compiler probably expects another value there. Second - see if you can use parameterized queries for this. — S.L. Barth is on codidact.com, Feb 07 '19 at 18:49
I have 288 numbers, so i cut the image, only that... (1,...,22,23,24,...,288) — LeoHenrique, Feb 07 '19 at 18:52
@S.L.Barth, yes, i got this example on Google, but i think the parameter of the example was a string... So for me, i need an int. — LeoHenrique, Feb 07 '19 at 18:54
@GSerg i think not, because this link is solution for string.. I need int — LeoHenrique, Feb 07 '19 at 18:55
@LeoHenrique The type of the parameter does not matter. [Do not](https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause#comment173820_337817) use the accepted answer, use the most upvoted one. — GSerg, Feb 07 '19 at 18:56
The easiest way to do this is to use Dapper (actually, Dapper is the answer to a whole class of "easiest way to do X" (where X has something to do with data access). Dapper will take a collection of integers as a parameter that it sticks within the parentheses of a WHERE/IN clause. — Flydog57, Feb 07 '19 at 19:18

Derviş Kayımbaşıoğlu · Accepted Answer · 2019-02-07T21:17:35.353

0

Check this for cleaner solution, and with parameterized queries

Dictionary<string, object> pars = new Dictionary<string, object>();
StringBuilder sb = new StringBuilder();
for (int i = 0; i < idParameterList.Count; i++)
     pars.Add($"{{{i}}}", idParameterList[i]);
sb.Append(String.Join(",", pars.Keys.ToArray()));

var finalQuery = strQuery + sb.ToString() + ");";

 dbContext.Database.ExecuteSqlCommand(finalQuery , pars.Values.ToArray()).ToList();

where strQuery need to have select ... from ... where id IN (

edited Feb 07 '19 at 21:17

answered Feb 07 '19 at 18:53

Derviş Kayımbaşıoğlu

28,492
4
50
72

what would cmd be for me? – LeoHenrique Feb 07 '19 at 18:58
I changed it. can you please check again? – Derviş Kayımbaşıoğlu Feb 07 '19 at 19:07
i got this error: Must declare the scalar variable "@Id0". On this line dbContext.Database.ExecuteSqlCommand(sb.ToString(), pars.Values.ToArray()); – LeoHenrique Feb 07 '19 at 19:12
I am going to add fix for you. check it in a min – Derviş Kayımbaşıoğlu Feb 07 '19 at 19:13
the same error.. – LeoHenrique Feb 07 '19 at 19:21
@Simonare `ExecuteSqlCommand` expects the parameters to be in the form of `{0}` like what `string.Format` accepts. – GSerg Feb 07 '19 at 20:55
I edited my answer accordingly thank you – Derviş Kayımbaşıoğlu Feb 07 '19 at 21:16
first of all, i dont know if with my Visual Studio version i have permition to use this character: '$', for me, i have an error here (pars.Add($"{{{i}}}")... I have visual studio 2013 – LeoHenrique Feb 08 '19 at 11:59
1

i did it with this `pars.Add(i.ToString(), idParameterList[i]);`.. With that, is it still strings concatenation? If dont, i do it with your solution – LeoHenrique Feb 08 '19 at 12:08
yes it is but if you are concatenating variable which is defined by you, It does not cause you problems. You need to prevent using user inputs with concatenation – Derviş Kayımbaşıoğlu Feb 08 '19 at 14:16

LeoHenrique · Answer 2 · 2019-02-07T19:48:36.663

-3

I got it:

strQuery += "WHERE ligID IN (";    
strQuery += String.Join(",", lstLigID) + ");";

dbContext.Database.ExecuteSqlCommand(strQuery);

Thanks everyone who tried to help me

edited Feb 07 '19 at 19:48

answered Feb 07 '19 at 19:42

LeoHenrique

229
5
16

oh man, you are doing same mistake! don't you listen what we all said? string concatenation is the worst possible way of doing this. anyway If you like it go for it. What can we say!! By the way, If you feel that any of our answers has benefit for you at least consider to upvote. – Derviş Kayımbaşıoğlu Feb 07 '19 at 20:40
1

To future readers: while it may be safe to do this with a list of ints specifically, [it is not safe in general](https://stackoverflow.com/q/332365/11683). – GSerg Feb 07 '19 at 20:59
@Simonare "Votes cast by those with less than 15 reputation are recorded, but do not change the publicly displayed post score.". I understood what all of you said, but I couldn't do what you said and this way works for me.. All of this is new for me and your ignorance is making things worse ... I'm trying to learn, I know some things, others (like this one) are very difficult for me ... Do not think I ignored what you said and the knowledge of you made me open my mind to learn these things that said that in the future I can improve my code, but for this moment, what I did, solve my problem... – LeoHenrique Feb 08 '19 at 11:51
So, i'll try again a lot of times with your solution and when I can use your solution, you can rest easy that I'll accept you awnser like solved for my problem – LeoHenrique Feb 08 '19 at 11:57

WHERE with IN Clause - C#

2 Answers2