CODEWITHSUNDEEP
c#dapper
anthv123
anthv123

Reputation: 523

C# Dapper query using WHERE IN

I am trying to perform a dapper query like this:

string query = "select * from MyTable where someNumber in @Nums;";
...
connection.Query<ReturnObj>(query, new {Nums = nums})

And I am getting a MySql syntax error if nums is empty. It looks like Dapper changes the query to look like this: WHERE 1 = 0) so I am guessing it the left ( is missing, which is causing the syntax error. Yes, I realize I could just check if the collection is empty before executing the query, but I would rather not if I don't have to.

Upvotes: 2

Views: 1951

Answers (1)

Bradley Grainger
Bradley Grainger

Reputation: 28162

This is a bug in Dapper where it creates a SQL statement that is invalid for MySQL Server 5.6 (and earlier).

Workarounds:

  • Upgrade to MySQL Server 5.7 (which accepts the SQL Dapper generates and returns the expected results)
  • As you said, check if the collection is empty before executing the query

A variant of checking if the collection is empty (that can be useful if you have a complex query, NOT IN, etc.):

  var numsSql = nums.Any() ? "@Nums" : "(select null)";
  var query = $"select * from MyTable where someNumber in {numsSql};";
  conn.Query(query, new { Nums });

Upvotes: 3

Related Questions