Non-parameter SQL Variables with MySql Connector/Net and Dapper?

Question

The code below generates the following error:

Parameter '@ID' must be defined.

Am I doing something wrong or is not it possible to use variables in the SQL query with MySQL which aren't Dapper parameters?

In this example, @Slug is a Dapper parameter, but @ID is not. I'm using MySQL so I don't need to DEFINE @ID - it get's defined in the first use.

var sql = @"SELECT @ID := id, slug, Title, Text FROM posts WHERE slug = @Slug;  SELECT * FROM comments where postid = @ID;";
using (var connection = GetOpenConnection())
{
    var posts = connection.QueryMultiple(sql, new { Slug = slug })
        .Map<Post, Comment, int>
        (
        Post => Post.ID,
        Comment => Comment.ID,
        (post, comments) => { post.Comments = comments; }
        );
    return posts.FirstOrDefault();
}

Answer 1

It turns out "MySql Connector/Net" generates the error.

In order to use Non-parameter SQL Variables with MySql Connector/Net, you have to add the following option to your connection string:

• "Allow User Variables=True"

See: http://blog.tjitjing.com/index.php/2009/05/mysqldatamysqlclientmysqlexception-parameter-id-must-be-defined.html

Answer 2

I don't think this is a dapper issue; dapper just takes the SQL you offer, and adds any members from the "args" object that it obviously see in the SQL.

The way to investigate this is to try running the same with DbCommand directly - my guess is that it will fail identical. There is going to be some SQL trick to make it work, but that is between you and MySQL. All dapper is doing is:

• creating a command with your sql
• defining a parameter called "Slug" and setting the value
• invoking the sql and parsing the results

it doesn't touch "ID", and it is correct that it doesn't do so.