Error when execute delete

[jasonlaw]

[Entity]
public interface ICommunity
{
   [OneToOne]
   IUnitAccount UnitAccount { get; set; }
}

[Entity]
public interface IUnitAccount
{
   [Primary] 
   ICommunity Community { get; set; }
}

[Entity]
public interface IUnit
{
   [Primary]
   string Id { get; set; }

   IUnitAccount UnitAccount { get; set; }
}

// reset and clean up all previous records            
  var queryDeleteUnits = session.EntitySet<IUnit>().Where(x => x.UnitAccount == unitAccount).Select(x => x.Id);
  session.ExecuteDelete<IUnit>(queryDeleteUnits);

You can't specify target table 'Unit' for update in FROM clause 
[9/16/2021 8:38:28 AM] ErrorKind:Internal 
Vita.Entities.DataAccessException: You can't specify target table 'Unit' for update in FROM clause
 ---> MySql.Data.MySqlClient.MySqlException (0x80004005): You can't specify target table 'Unit' for update in FROM clause
   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.ExecuteReader()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at Vita.Data.Driver.DbDriver.ExecuteCommand(IDbCommand command, DbExecutionType executionType)
   --- End of inner exception stack trace ---
   at Vita.Data.Driver.DbDriver.ExecuteCommand(IDbCommand command, DbExecutionType executionType)
   at Vita.Data.MySql.MySqlDbDriver.ExecuteCommand(IDbCommand command, DbExecutionType executionType)
   at Vita.Data.Runtime.Database.ExecuteDataCommand(DataCommand command)
Exception data (Vita.Entities.DataAccessException): 
  DbCommand = CommandType: Text
CommandText: DELETE FROM "communityv3staging_3"."Unit" 
    WHERE "Id" IN (SELECT  u$."Id"
FROM "communityv3staging_3"."Unit" u$
     INNER JOIN "communityv3staging_3"."UnitAccount" ua$ ON ua$."Community_Id" = u$."UnitAccount_Community_Id"
WHERE ua$."Community_Id" = @P0)
Parameters: 
    @P0 = (DbNull)

Exception data (MySql.Data.MySqlClient.MySqlException): 
  Server Error Code = 1093 

Thank you.

[rivantsov]

https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause

the workaround would be - execute subselect, return list of IDs to client, and then execute Delete with list of IDs

[jasonlaw]

Thanks!