C# Dapper query using WHERE IN

Question

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.

nums = numIds.ToReadOnlyCollection(), where numIds is an IEnumerable — anthv123
– anthv123, Commented Aug 16, 2012 at 23:05
It's more rather the fact that I think Dapper should handle a situation like this rather than me having to fix it's mistake. — anthv123
– anthv123, Commented Aug 16, 2012 at 23:19

Bradley Grainger · Accepted Answer · 2017-10-27 00:39:53Z

3

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 });

answered Oct 27, 2017 at 0:39

Bradley Grainger

28.3k4 gold badges95 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

C# Dapper query using WHERE IN

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related