how to send List to IN operator my sql

Question

I have int list and I need to convert it so it will be possible to send it to IN operator from c# code. I tried a lot of manipulate but got error every time.

string idsList = "(" + string.Join(",", ids.Select(id => $"'{id.ToString()}'")) +")";

 var sql = @"SELECT * from user
WHERE user.id IN (@idsList))";
        using (var connection = OpenConnection())
        {
            var results = await connection.QueryAsync<UserQueryResponse>(sql, new
            {
                idsList= idsList,
            });
            return results.Select(raw =>raw.CreateModel());
        }
    }

The column type of user.id is string

"but got error every time" - What error? Of course, providing that gives a lot of context to the issue you're seeing... — Broots Waymb, Mar 11 '19 at 15:50
It seems that you have complicated your code a lot. It is just _string.Join(",", ids)_ — Steve, Mar 11 '19 at 15:51
nothing, Just not getting result from the query, if I put the same value hard-coded there is a result.. — j.her, Mar 11 '19 at 15:51
Then compare the hard-coded query with the generated query, just before it runs? Without seeing your code we can only guess... — C.Evenhuis, Mar 11 '19 at 15:52
What is your query ? How is your code ? Could you share your SQL and code that calls DB? — ibrahimozgon, Mar 11 '19 at 15:52
1 - Edit the question and add the full SQL executed against the mysql. 2- What is the column type in the mysql table? — Cleptus, Mar 11 '19 at 15:55

ibrahimozgon · Answer 1 · 2019-03-11T16:42:17.040

0

I assume you are using Dapper. Have a look at this answer. Try deleting the parenthesis in your query.

var sql = @"SELECT * from user WHERE user.id IN @idsList";
        using (var connection = OpenConnection())
        {
            var results = await connection.QueryAsync<UserQueryResponse>(sql, new
            {
                idsList= idsList,
            });
            return results.Select(raw =>raw.CreateModel());
        }
    }

edited Mar 11 '19 at 16:42

answered Mar 11 '19 at 16:00

ibrahimozgon

1,137
1
12
19

I changed my answer because the question was changed :) – ibrahimozgon Mar 11 '19 at 16:36
Now it is better _assuming Dapper_ but still you have a typo in the query. Missing the open parenthesys in the IN statement – Steve Mar 11 '19 at 16:39

Cleptus · Answer 2 · 2019-03-11T17:21:30.100

You should not use the parameter delimiter (@) if your query is not parametrized.

Warning: Do note that if the ids list were not of type int you could have had a SQL Injection problem.

List<int> ids = new List<int>{ 7, 14 ,21 };
string idsList = "(" + string.Join(",", ids.Select(id => $"'{id.ToString()}'")) +")";
/* Your idsList variable will contain "('7','14','21')" */
var sql = @"SELECT * from user WHERE user.id IN " + idsList;

A better approach would be using parameters:

StringBuilder idsList = new StringBuilder("in (");

for (int i=0; i< ids.Count; i++)
{
    // Dynamically write the IN clause
    if (i > 0) ? idsList.Append(",");
    idsList.Append("@param" + i.ToString());
    // Add the parameter that was added to the IN
    command.Parameters.AddWithValue("@param" + i.ToString(), idsList[i])
}
idsList.Append(")");
/* idsList will cointain "in (@param0, @param1, @param2)" */

how to send List to IN operator my sql

2 Answers2