Error for subquery update

[jasonlaw]

My entity models:

[Entity]
[EntitySavingEvent(nameof(BillingModule.OnSavingInvoice))]
public interface IBillingInvoice
{
    [PrimaryKey, Size(20)] string InvoiceNo { get; set; }
    IBillingAccount Account { get; set; }
    IUnit Unit { get; set; }
    [Nullable] IMemberInCommunity BillTo { get; set; }  
    [Size(200)] string Description { get; set; }
    // The amount is in cent.e.g. 100 = RM1.
    int Amount { get; set; }
    int PaidAmount { get; set; }
    [DbComputed(kind: DbComputedKind.NoColumn)]
    [SqlExpression(DbServerType.MySql, "{table}.Amount - {table}.PaidAmount")]
    [SqlExpression(DbServerType.SQLite, "{table}.Amount - {table}.PaidAmount")]
    long OutstandingAmount { get; }
    bool IsFullyPaid { get; set; }
    IList<IBillingInvoiceReceipt> Receipts { get; }
    [Utc, Auto(AutoType.CreatedOn)] DateTime CreatedOn { get; }
    [ComputedClientTime] DateTime CreatedOnClientTime { get; }
    [Auto(AutoType.CreatedBy), Size(nameof(IAuth.LoginId))] string CreatedBy { get; }
}

[Entity]
[UniqueClusteredIndex("Invoice, Receipt")]
[EntitySavingEvent(nameof(BillingModule.OnSavingInvoiceReceipt))]
public interface IBillingInvoiceReceipt
{
    [PrimaryKey, AutoID] string Id { get; }
    IBillingInvoice Invoice { get; set; }
    IBillingReceipt Receipt { get; set; }
    int Amount { get; set; }
    [Utc, Auto(AutoType.CreatedOn)] DateTime CreatedOn { get; }
    [ComputedClientTime] DateTime CreatedOnClientTime { get; }
    [Auto(AutoType.CreatedBy), Size(nameof(IAuth.LoginId))] string CreatedBy { get; }
}

BillingInvoiceReceipt saving events

internal static void OnSavingInvoiceReceipt(IBillingInvoiceReceipt receipt)
  {
      var session = EntityHelper.GetSession(receipt);
      var updateInvoices = session.EntitySet<IBillingInvoice>()
                                  .Where(x => x == receipt.Invoice)
                                  .Select(x => new { x.InvoiceNo, PaidAmount = x.Receipts.Sum(r => r.Amount) });
      session.ExecuteUpdate<IBillingInvoice>(updateInvoices);
  }

Error when saving.

==== Error Log Started 12/31/2021 6:38:14 AM =========== Unknown column 'bi$.InvoiceNo' in 'where clause' [12/31/2021 6:39:08 AM] ErrorKind:Internal Vita.Entities.DataAccessException: Unknown column 'bi$.InvoiceNo' in 'where clause' ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'bi$.InvoiceNo' in 'where clause' at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, 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: UPDATE "communityv3development"."BillingInvoice" SET "PaidAmount" = (SELECT SUM(bir$."Amount") FROM "communityv3development"."BillingInvoiceReceipt" bir$ WHERE bir$."Invoice_InvoiceNo" = bi$."InvoiceNo") WHERE (bi$."InvoiceNo" = @P0 OR (bi$."InvoiceNo" IS NULL) AND (@P0 IS NULL)); Parameters: @P0 = "0001-2021-0566"

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

[jasonlaw]

Hi @rivantsov ,

Would there be any fixed for this issue? Or I should rewrite my code?

Thanks!

[rivantsov]

sorry with the delay with response, holidays and all that. I am looking into this; looks like SQL error, generated sQL is invalid, I will try to fix it, soon if I can, have a couple of other outstanding bugs, wanna batch fixes together. For now, if you can, do some workaround.

[jasonlaw]

No worries, I will work with workaround for now. Thanks for looking into this, really appreciate it.

[rivantsov]

Hi finally looked at this, and sorry it took so long. I have an answer, and I am afraid you not gonna like it. This update query is not supposed to work for MySql. The engine should detect that this query results in SQL statement that uses Update-From-Select type of Update statements, which is NOT supported by MySql. But because of the bug in the engine, it failed to detect it upfront (that this form required), tried to build it and ended up with invalid SQL. So you have to use a usual method - load invoice, run Sum query to get Total of receipts, update the invoice and save it.

[jasonlaw]

Thanks for the clarification.