Dapper with MySQL and ASP.NET CORE 2 - Insert Model Fails

Question

I'm using ASP.NET CORE 2 with MySQL 5.6.27, Dapper 1.50.2 and Dapper.Contrib 1.50.0. I'm getting an error while trying to perform basic insert operation using model, however if I use plain INSERT INTO SQL query, the operation is successful.

This is the error I'm getting:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE 1 = 0), (SELECT NULL WHERE 1 = 0), (SELECT NULL WHERE 1 = 0), (SELECT NULL' at line 1

Code:

    using (IDbConnection dbConnection = Connection)
    {
         dbConnection.Open();
         var newUser = dbConnection.Insert(entity); // error on this line
}

Model class

public partial class Aspnetusers
{
        [Key]
        public int Id { get; set; }
        public string UserName { get; set; }
        public int AccessFailedCount { get; set; } = 0;
        public string Email { get; set; }
        public bool EmailConfirmed { get; set; } = false;
        public bool IsActive { get; set; } = true;
        public bool IsRootUser { get; set; } = false;
        public bool LockoutEnabled { get; set; } = false;
        public string PasswordHash { get; set; }
        public bool PhoneNumberConfirmed { get; set; } = false;
        public bool TwoFactorEnabled { get; set; } = false;
}

I tried with updating Dapper and Dapper.Contrib to 1.50.5, but still the same error.

Please suggest what is incorrect here.

Regards, Aryan

Follow this [](https://stackoverflow.com/questions/48640896/dapper-contrib-insert-exception) to share us the generated `SQL Statement`. — Edward, Aug 16 '18 at 07:03
@Edward, If I'm correct then MiniProfiler is not available for ASP.NET CORE 2.0 — Arayn, Aug 16 '18 at 14:07
Yes, try to check running query in mysql. https://stackoverflow.com/questions/568564/how-can-i-view-live-mysql-queries — Edward, Aug 16 '18 at 15:42
tried to run 'SHOW FULL PROCESSLIST;' command, but it doesn't show the query created from application i.e. by .Add(entity) method from Dapper.Contrilib — Arayn, Aug 17 '18 at 08:34

score 0 · Answer 1 · answered Aug 28 '18 at 17:57

You're running into Dapper issue 565: Querying for empty list fails with MySQL Server 5.6.

Dapper's SQL generation (for empty enumerables) is incompatible with MySQL Server 5.6; you either need to update to MySQL Server 5.7 or later, or write the SQL by hand.

Dapper with MySQL and ASP.NET CORE 2 - Insert Model Fails

1 Answers1