EntityFrameworkCore.DbUpdateException: Unable to delete row, SQL Syntax error

Question

I am using an ASP.NET Core Web API with Entity Framework Core (pomelo). I have a MariaDB database. I use Swagger UI to explore my API, as per the template. When I try to use it to delete a row, I get the following error:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.

MySqlConnector.MySqlException (0x80004005): 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 'RETURNING 1' at line 3

at MySqlConnector.Core.ServerSession.ReceiveReplyAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in //src/MySqlConnector/Core/ServerSession.cs:line 894 at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in //src/MySqlConnector/Core/ResultSet.cs:line 37 at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in //src/MySqlConnector/MySqlDataReader.cs:line 130 at MySqlConnector.MySqlDataReader.InitAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in //src/MySqlConnector/MySqlDataReader.cs:line 483 at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in //src/MySqlConnector/Core/CommandExecutor.cs:line 56 at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in //src/MySqlConnector/MySqlCommand.cs:line 357 at MySqlConnector.MySqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 350 at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)

The delete should be handled here in my controller and repository, like this:

    [HttpDelete("alertId")]
    public async Task<IActionResult> DeleteAlert(int alertId)
    {
        var alert = await _dataRepository.GetAlertAsync(alertId);

        if (alert is null)
        {
            return NotFound("Alert not found");
        }

        await _dataRepository.DeleteAlertAsync(alert);

        return NoContent(); 
    }

and this

public class AlertRepository (IrtsContext context) : IDataRepositoryAlerts
{
    readonly IrtsContext _alertContext = context;

    public async Task DeleteAlertAsync(Alert entity)
    {
        if (entity != null)
        {
            _alertContext.Remove(entity);
            await _alertContext.SaveChangesAsync();
        }
        else
        {
            throw new NotImplementedException();
        }
    }
}

I do not understand this. I believe it is my dbContext that handles the "saving the entity changes". How can I have a SQL syntax error? I cannot find "Returning 1" anywhere in my code.

I have tried deleting the row manually in my database. That works. All other operations (GET, POST and PUT) work just fine.

I have tried running this with holding points to see where the error occurs but everything seems to execute without issue.

I am grateful for any hints. I am obviously very new to this ;)

Edit: MariaDB version 11.2.2 Edit2: This is my Alert class:

    public partial class Alert
{
    public int AlertId { get; set; }
    public DateTime? Zeitpunkt { get; set; }
    public string? Quelle { get; set; }
    public string? AlertStatus { get; set; }
    public string? AlertTyp { get; set; }
    public string? BetroffeneSysteme { get; set; }
    public virtual ICollection<Vorfall> Vorfalls { get; set; } = new List<Vorfall>();
    }

and this is its entity configuration:

modelBuilder.Entity<Alert>(entity =>
{
    entity.HasKey(e => e.AlertId).HasName("PRIMARY");
    entity
        .ToTable("alert")
        .HasCharSet("utf8mb4")
        .UseCollation("utf8mb4");
    entity.Property(e => e.AlertId)
        .HasColumnType("int(11)")
        .HasColumnName("AlertID");
    entity.Property(e => e.AlertStatus).HasMaxLength(255);
    entity.Property(e => e.AlertTyp).HasMaxLength(255);
    entity.Property(e => e.BetroffeneSysteme).HasMaxLength(255);
    entity.Property(e => e.Quelle).HasMaxLength(255);
    entity.Property(e => e.Zeitpunkt).HasColumnType("datetime");
});

edit3: I found the parameterized query. It goes thus: [The error in the log with sql query]

Edit 4: copying the query into workbench gives the following error:

It appears that the problem really is the "RETURNING 1" and I must admit I have no idea what it is for. Is there a way to remove it? I have found no way to edit the EF queries themselves. Alternatively I might need to try a different database. Thank you for your help!

Answer 1

Based on your investigation, I suspect your DB version is older than you think. You could try adding the ServerVersion.AutoDetect(connectionString) to your EF core MySql configuration. See if that influences EF core to write SQL that in understandable to your DB version? It would look something like this when you are configuring services:

Services.AddDbContext<YourDbContext>(options =>
{
    //...your other configs
    var connectionString = builder.Configuration.GetConnectionString("or however you get the connection string...");
    options.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString));
});

You can also try setting the version explicitly like this:

services.AddDbContext<YourDbContext>(options => 
        options.UseMySql(
            Configuration.GetConnectionString("how ever you get this"),
            mySqlOptions => mySqlOptions.ServerVersion(new Version(10, 0, 40, ServerType.MariaDb)
        )
    );

Use which ever version you have, though