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

Answer 1

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.