How to use Dapper with mysql when the sql statement contains @

Question

I have trouble when my SQL statement contains the '@'

It seems like that Dapper used the '@' and throw an exception:"@rownum" must be declined.

But it's not a Dapper parameter.

I need some help. Here is my code:

var sqlStr = @"SELECT 
                    @rownum := @rownum +1 AS rownum, 
                    e.* FROM (SELECT @rownum := 0) r,
                    (SELECT 
                        f.nickname,
                        u.charm_value 
                    FROM 
                        user_info u 
                        LEFT JOIN fans_info f ON u.openid=f.openid
                    ORDER BY 
                        u.charm_value DESC,u.create_time DESC LIMIT 0,500) e ";
return conn.Query<Top500Response>(sqlStr).ToList();

Answer 1

Dapper will pass this through as-is, since it's not bound to a parameter (see How do I escape a '@' in a Dapper query?).

I think the error is actually originating from MySQL, and what you need to do is set:

Allow User Variables=True

in the connection string (see allow-user-variables)