Incorrect configured One-To-One navigation generates invalid SQL instead of error

[tiberhealth]

Steps to reproduce

Create a one-to-one navigation in EF Core with the identify field as the FK value and the non-identity field as the Principal.

This will result in the following SQL statement:

INSERT INTO `CategoryDim` (... Proper Field List ...)
      VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13);
      SELECT `DimId`
      FROM `CategoryDim`
      WHERE ROW_COUNT() = 1 AND ;

Note the hanging AND clause.

I agree that this is a bad configuration, and the navigation should be fixed. It would be nicer if a proper error occurred, letting it be known of the configuration, instead of getting an Invalid Syntax error from the database.

The issue

The GetValueGenerationStrategy in MYSqlPropertyExtensions is used to determine if the field as an identify field and requires the LAST_INSERT_ID() clause.

Method Start line 77, Issue start line 112

      internal static MySqlValueGenerationStrategy GetValueGenerationStrategy(
          this IReadOnlyProperty property,
          in StoreObjectIdentifier storeObject,
          [CanBeNull] ITypeMappingSource typeMappingSource)
      { 

...

          if (property.ValueGenerated == ValueGenerated.OnAdd)
          {
              if (table.StoreObjectType != StoreObjectType.Table
                  || property.TryGetDefaultValue(storeObject, out _)
                  || property.GetDefaultValueSql(storeObject) != null
                  || property.GetComputedColumnSql(storeObject) != null
                  || property.GetContainingForeignKeys()
                      .Any(
                          fk =>
                              !fk.IsBaseLinking()
                              || (StoreObjectIdentifier.Create(fk.PrincipalEntityType, StoreObjectType.Table)
                                      is StoreObjectIdentifier principal
                                  && fk.GetConstraintName(table, principal) != null)))
              {
                  return MySqlValueGenerationStrategy.None;
              }

              if (IsCompatibleIdentityColumn(property))
              {
                  return MySqlValueGenerationStrategy.IdentityColumn;
              }

If the field is marked as an FK to a principal, it returns None and does not create the rest of the Identity clause. The identity check is after.

I agree that the processing should stop, but it does not and continues with the incorrect SQL statement for obtaining the identity keys.

Exception message:

02:35:08.9999   Update                          Error                           An exception occurred in the database while saving changes for context type 'Tiberhealth.Common.Database.DataWarehouse.DwContext'.
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 ';

INSERT INTO `CategoryDim` (`Active`, `AoaWeight`, `BoardWeight`, `CreatedDate' at line 3
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 175
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 133
   at MySqlConnector.MySqlDataReader.NextResultAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 107
   at MySqlConnector.MySqlDataReader.NextResultAsync(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 50
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeAsync(RelationalDataReader reader, CancellationToken cancellationToken)
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeAsync(RelationalDataReader reader, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken) An error occurred while saving the entity changes. See the inner exception for details.
02:35:09.0001   ExamSoftXTractCartridge         Information                     Exception processing Queue Item: ExamId: xxxx PostingId: xxxx
02:35:09.0001   ExamSoftXTractCartridge         Error                           An error occurred while saving the entity changes. See the inner exception for details.
02:35:09.0089   ExamSoftXTractCartridge         Information

Stack trace: See description

Further technical details

MySQL version: Oracle OCI MySQL 8.0.32 With Heatwave Operating system: Oracle OCI PaaS Pomelo.EntityFrameworkCore.MySql version: 7.0.0 Microsoft.AspNetCore.App version: Net7.0

Other details about my project setup:

I have forked and am currently reviewing the EFCore.MySql project to determine the proper method for sending exceptions to keep with the coding style and practices. I have/had several contractors and JR developers, and this is all over the place in our codebase. This was accepted (albeit incorrectly) in version 6.0.2.

Thank, Bryan M Lenihan

[tiberhealth]

Edited to remove IP and identifying items from exxceptions and code examples.