Syntax-related exceptions thrown?!

[aradalvand]

Hi there. I've been using this library for a long time but recently many batch operations that used to work seamlessly are now throwing obscure syntax exceptions. I was surprised to see that this hasn't been reported.

Minimal reproduction: https://github.com/aradalvand/efcorebulkextensions-issue

Steps to reproduce:

• Clone the repo
• Run dotnet restore
• Adjust the connection string in AppDbContext.OnConfiguring located in the Program.cs file
• Apply the migration
• Run dotnet run

I have this:

db.Carts
    .Where(c => c.UserId == 1)
    .SelectMany(c => c.Items)
    .BatchDelete();

Exception information:

Failed executing DbCommand (77ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      DELETE 0
      FROM "Carts" AS c
      INNER JOIN "CartItems" AS c0 ON c."Id" = c0."CartId"
      WHERE c."UserId" = 1
Unhandled exception. Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "0"

POSITION: 8
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|211_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlRaw(DatabaseFacade databaseFacade, String sql, IEnumerable`1 parameters)
   at EFCore.BulkExtensions.IQueryableBatchExtensions.BatchDelete(IQueryable query)
   at Program.<Main>$(String[] args) in /home/arad/temp/Program.cs:line 6
  Exception data:
    Severity: ERROR
    SqlState: 42601
    MessageText: syntax error at or near "0"
    Position: 8
    File: scan.l
    Line: 1176
    Routine: scanner_yyerror

[aradalvand]

@borisdj Sorry to tag you, I am perfectly aware that you are most likely very busy and have no obligation to check out the issues here quickly. But I'm just curious whether this is an issue on my part or if it's a bug? That would be great to know.

Thank you in advance :)

[borisdj]

@aradalvand PG does not support DELETE JOIN: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-delete-join It would required syntax Restructure for this type of Batch.

[aradalvand]

@borisdj Thank you for the response. What would you suggest could be done about this right now?

[borisdj]

Not sure, will see if a Restructure method could be made. I've done similary already one for Update. But can't put any timeframe. If you want to try and if manage make a PR.

Or you could try to change Query. Maybe load required Carts into memory, then iterate for each and call BulkDelete with Where on ChartId

[aradalvand]

Since ExecuteUpdate has now been introduced in EF Core 7, you can probably close this issue now. Thank you!