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 [](stackoverflow.com/questions/48640896/…) to share us the generated SQL Statement. — Edward
@Edward, If I'm correct then MiniProfiler is not available for ASP.NET CORE 2.0 — Arayn
Yes, try to check running query in mysql. stackoverflow.com/questions/568564/… — Edward
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

Bradley Grainger Bradley Grainger · Accepted Answer · 2018-08-28T17:57:53

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 Answers