Description:
When an EntityFramework Core entity without values is added via a SaveChanges() call on the context, the generated query will contain the "DEFAULT VALUES" expression. The MySQL server does not support this syntax, it must be "VALUES()" instead.
The "problem" is that after the "INSERT INTO" query has been send, the EntityFramework will automatically send a "SELECT" query afterwards to get the generated values for the new added entity. There is/was a fix in <https://github.com/mysql/mysql-connector-net/blob/6.9/Source/MySql.Data/command.cs#L138> which handle such a case. However, this "DEFAULT VALUES" part is not at the end of the query. As you see in the following exception, the "DEFAULT VALUES" part was in the middle of the query, so the fix in the MySql.Data.MySqlClient.MySqlCommand class doesn't work anymore:
MySql.Data.MySqlClient.MySqlException: 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 'DEFAULT VALUES;
SELECT `Id`
FROM `Invoices`
WHERE ROW_COUNT() = 1
AND `Id`=LAST' at line 2
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
When you use the [DatabaseGenerated(DatabaseGeneratedOption.None)] attribute on the properties of the entity, it will not generate the SELECT query. However, when using this attribute the value will be missing in the tracked entity of EntityFramework.
Versions:
MySql.Data.EntityFrameworkCore: 8.0.22
Microsoft.EntityFrameworkCore: 3.1.9
MySQL Server version: 8.0.21 Gentoo Linux mysql-8.0.21-r1
C# target framework: netcoreapp3.1
How to repeat:
Create the following "Invoices" table:
CREATE TABLE Invoices
(
Id int AUTO_INCREMENT PRIMARY KEY
);
Create the following "Invoice" C# class for the EntityFramework:
public class Invoice
{
public int Id { get; set; }
}
Create the following DbContext class for the EntityFramework:
public class MyContext : DbContext {
public DbSet<Invoice> Invoices {get; set;}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
base.OnConfiguring(optionsBuilder);
optionsBuilder.UseMySQL("server=localhost;database=test;"); // or any other valid connection string
}
protected override void OnModelCreating(ModelBuilder modelBuilder) {
}
}
Create a class with the following Main method:
public static void Main()
{
using (MyContext context = new MyContext())
{
Invoice invoice = new Invoice();
context.Invoices.Add(invoice);
context.SaveChanges();
}
}
