Building SQL "where in" statement from list of strings in one line?

Question

I have a List(Of String) which corresponds to "types" on our database table.

We are using the DB2 ADO.NET provider and my final query needs to look something like this:

select * from table where type in (@type1, @type2, @type3, @type4)

In the past, I've built the list of query parameters / host variables using a ForEach loop, but I would really like to figure out a way to build them in one line. Of course, I can join all of the strings, but adding the "@" and the incrementing digit is giving me headaches.

Anyone have any ideas on how to do this?

Solution 1

Won't something like this work?

var inList = "(" + string.Join(", ", typeList.Select(t => "@" + t)) + ")";

Edit

Based on your comment, how about this?

var inList = "(" + 
    string.Join(", ", Enumerable.Range(1, argCount).Select(i +> "@type" + i)) +
    ")";

Solution 2

string dbCommand = 
    string.Format("select * from table where type in ({0})", string.Join(",", typeList.Select(p => "@" + p));

Solution 3

This is how I generally do this

string.Join(",", items.Select(i => $"'{i}'");

Comments

[Brian Dishaw]

I've have had a similar issue in the past and building these up by hand in a pain. I really like the elegence of the linq method!

[Dan J]

Good ol' LINQ-to-String to the rescue. :)

[TaylorOtwell]

Well, I actually need the parameters to literally be @type1, @type2, etc. With the numbers and all. I'm just trying to create a parameter for each type and I'll fill each parameter with the actual values of the types before executing the query.