How can I use the Dapper with a list to create an IN statement for the WHERE clause?

c# dapper

Question

I'm passing an int list to Dapper Where IN clause. I get error because it is and comma-separated string.

JObject step1Form = JObject.Parse(Request.Cookies["step1"]);
IList<int> step1List = step1Form.Root.Select(c => (int)c).ToList();

// "1, 22, 31, 45, 55"

I get my int list which is a comma-separated string.

var hazardFormList = dbConnection.Query<FLViewModel>(
"SELECT * FROM FLHA WHERE Id IN (@hazardList) ORDER BY SortOrderId", new { @hazardList = step1List  }).ToList();

How can I pass it in the Dapper query? I'm getting error, obviously passing string inside IN.

Any help?

1
0
5/31/2018 3:25:33 PM

Accepted Answer

Dapper supports this directly. For example...

string sql = "SELECT * FROM SomeTable WHERE id IN @ids"
var results = conn.Query(sql, new { ids = new[] { 1, 2, 3, 4, 5 }});
353
1/5/2018 9:39:54 AM

Popular Answer

Directly from the GitHub project homepage:

Dapper allow you to pass in IEnumerable and will automatically parameterize your query.

connection.Query<int>(
    @"select * 
      from (select 1 as Id union all select 2 union all select 3) as X 
      where Id in @Ids", 
    new { Ids = new int[] { 1, 2, 3 });

Will be translated to:

select * 
from (select 1 as Id union all select 2 union all select 3) as X 
where Id in (@Ids1, @Ids2, @Ids3)

// @Ids1 = 1 , @Ids2 = 2 , @Ids2 = 3


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow