Bulk update does not work when targeting AWS Aurora/MySql

[paya-cz]

Description

I am running a code connecting to AWS Aurora cluster, running a MySql engine v5.6.10a.

This is how I set up a connection to the server:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    // See https://mysql-net.github.io/MySqlConnector/connection-options/ for more info
    var connectionStringBuilder = new MySqlConnectionStringBuilder
    {
        Server = Helper.DatabaseEndpoint,
        Port = 3306,
        Pooling = true,
        UserID = Helper.DatabaseUser,
        Password = Helper.DatabasePassword,
        SslMode = MySqlSslMode.VerifyCA,
        CACertificateFile = Path.Combine(Environment.CurrentDirectory, "Certificates", "rds-combined-ca-bundle.pem"),
        ConvertZeroDateTime = true
    };

    optionsBuilder
        .UseMySql(connectionStringBuilder.ConnectionString);
}

Then, I set up model for 2 different entities, that go into tables in 2 different schema. Eg. entityA into schemaA, and entityB goes into schemaB. Running the bulk update statement throws a MySql.Data.MySqlClient.MySqlException with message "No database selected", even though the entity has been configured via .ToTable(tableName, schema) to target a specific schema. The code doing the update is pretty much equivalent to:

ctx.entityA.Where(x => listOfIDs.Contains(x.Id))
    .Update(x => new EntityA() { SomeProperty = null });

Note that a similar code needs to be available for entityB also.

However, if I modify the MySqlConnectionStringBuilder to also include Database property, such as:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    // See https://mysql-net.github.io/MySqlConnector/connection-options/ for more info
    var connectionStringBuilder = new MySqlConnectionStringBuilder
    {
        Server = Helper.DatabaseEndpoint,
        Port = 3306,
        Database = schemaB
        Pooling = true,
        UserID = Helper.DatabaseUser,
        Password = Helper.DatabasePassword,
        SslMode = MySqlSslMode.VerifyCA,
        CACertificateFile = Path.Combine(Environment.CurrentDirectory, "Certificates", "rds-combined-ca-bundle.pem"),
        ConvertZeroDateTime = true
    };

    optionsBuilder
        .UseMySql(connectionStringBuilder.ConnectionString);
}

Then calling the Update code as above yields a different exception: MySql.Data.MySqlClient.MySqlException Message: Table 'schemaB.entityA_table` doesn't exist

At this point, it's pretty clear that EFPlus is just taking whatever is in the connection string builder's Database property, and using that as a schema when making calls. It seems EFPlus is completely ignoring schema defined by ModelBuilder's .ToTable.

I confirmed this theory by modifying the ConnectionStringBuilder to use: Database = schemaA At which point the .Update call works as expected.

Further technical details

• EF version: EF Core v2.2.4
• EF Plus version: 1.8.27
• EF Provider: Pomelo.EntityFrameworkCore.MySql v2.2.0
• Database: AWS Aurora cluster, MySql engine v.5.6.10a

[JonathanMagnan]

Hello @paya-cz ,

Our library should take what is stored in the relation table name: https://github.com/zzzprojects/EntityFramework-Plus/blob/master/src/shared/Z.EF.Plus.BatchUpdate.Shared/BatchUpdate.cs#L830

Is this issue can be reproduced using directly MySQL? We don't have any AWS Aurora clusted for testing.

[paya-cz]

@JonathanMagnan The thing is, this is not about table name, but schema name. The code you showed just retrieves table name. But you can specify schema name also via .ToTable method, as shown here:

https://docs.microsoft.com/en-us/ef/core/modeling/relational/tables https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationalentitytypebuilderextensions.totable?view=efcore-2.1

This is what I'm using and what seems to be causing issue.

Also, I had issues using Owned entities, seems like this is not supported by EFPlus either? Basically when I was doing a bulk update on an entity that owns another entity, the generated SQL contained duplicate column for the master entity's primary key. Owned entities are supported since EFCore 2.0, maybe this is not yet supported in EFPlus?