[Error] Set null seems not updating

[jasonlaw]

I have the following entity schema.

[Entity]
public interface IJointCollection
{
   IList<IBillingInvoice> Invoices { get; }
}

[Entity]
public interface IBillingInvoice
{
   IJointCollection  JointCollection {get; set;}
}

So in order to delete record from JointCollection, I need to first make sure the BillingInvoice.JointCollection is empty.

        private static void DeleteJointCollections(IEntitySession session, IList<IBillingJointCollection> jointCollections)
        {
            var invoices = jointCollections.SelectMany(x => x.Invoices).ToList();
            foreach (var invoice in invoices)
            {
                invoice.JointCollection = null;
            }
            session.SaveChanges();

            var taskList = new List<Task>();
            foreach ( var collection in jointCollections)
            {
                var task = BillplzService.Instance.DeactivateCollectionAsync(collection);
                taskList.Add(task);
                session.DeleteEntity(collection);
            }
            Task.WaitAll(taskList.ToArray());
            session.SaveChanges();
        }

From the log file we can see that 2 records are retrieved from the BillingInvoice, but there is no update command to set the JointCollection to empty before delete execution. Could it be due to the framework failed to mark the dirty flag for this? Any workaround?

-- SaveChanges completed. Records: 2, Time: 1072 ms. ------------
SELECT  "InvoiceNo", "Description", "Amount", "PaidOn", "PaidUpdatedBy", "CreatedOn", "Collection_Id", "JointCollection_Id", "BillplzBill_Id", "Unit_Id", "BillTo_Key"
FROM "communityv3staging_4"."BillingInvoice"
WHERE "JointCollection_Id" IN ('03czeyug')
ORDER BY "JointCollection_Id"; 
 -- Time 245.6404 ms; 2 row(s), [2021/10/02 04:42:36] 

-- SaveChanges starting, 1 records ------------
 -- Failed command text: 
DELETE FROM "communityv3staging_4"."BillingJointCollection" 
    WHERE "Id" = @P0; 
-- Parameters: @P0='03czeyug' 

 -- Time 0 ms, [2021/10/02 04:42:37] 

Log file attached here: Log.txt

[rivantsov]

hm.. quite confusing.. if you set it to null then the member JointCollection should have [Nullable] attribute, I do not see how the first SaveChanges can work at all. Pls send me more complete ent definitions OR - Can you just put [CascadeDelete] attr on JointCollection member? this will then automatically delete child records

[jasonlaw]

Sorry, indeed there is a [Nullable] attribute for member JointCollection. In this case, the invoices can be grouped for payment, and the grouping could be changed. Due to that, we are not deleting the invoices but to change the collection reference.

Here is my original scheme:

    public interface IBillingCollectionBase
    {
        //[PrimaryKey, Auto(AutoType.NewGuid)] // From gateway
        [PrimaryKey, Size(100)] string Id { get; set; }

        IBillingAccount BillingAccount { get; set; }

        int TransactionFee { get; set; }

        [Size(50)] string Title { get; set; }

        [Auto(AutoType.CreatedBy), Size(nameof(IAuth.LoginId))]
        string CreatedBy { get; }

        [Utc, Auto(AutoType.CreatedOn)]
        DateTime CreatedOn { get; }

        [ComputedClientTime]
        DateTime CreatedOnClientTime { get; }

        [Utc]
        DateTime DueOn { get; set; }

        [ComputedClientTime]
        DateTime DueOnClientTime { get; }

    }

    [Entity]
    public interface IBillingCollection : IBillingCollectionBase
    {
        IList<IBillingInvoice> Invoices { get; }

    }

    [Entity]
    public interface IBillingJointCollection : IBillingCollectionBase
    {
        IList<IBillingInvoice> Invoices { get; }

        IMemberInCommunity BillTo { get; set; }
    }

    [Entity]
    public interface IBillingInvoice 
    {
        [PrimaryKey, Size(20)]
        [AutoNumber(4, AutoNumberFormat.YearOnly, ContextTag.CommunityId)] //eg, 0001-2021-9999
        string InvoiceNo { get; }

        [CascadeDelete]
        IBillingCollection Collection { get; set; }

        [Nullable]
        IBillingJointCollection JointCollection { get; set; }

        [Size(200)]
        string Description { get; set; }        

        [Nullable] IBillplzBill BillplzBill { get; set; }

        // The amount is in cent.e.g. 100 = RM1.
        int Amount { get; set; }

        IUnit Unit { get; set; }

        [Nullable] 
        IMemberInCommunity BillTo { get; set; }

        [Utc]
        DateTime? PaidOn { get; set; }

        [ComputedClientTime]
        DateTime? PaidOnClientTime { get; }

        [Nullable]
        string PaidUpdatedBy { get; set; }

        [Utc, Auto(AutoType.CreatedOn)]
        DateTime CreatedOn { get; }

        [ComputedClientTime]
        DateTime CreatedOnClientTime { get; }

    }

[rivantsov]

is it possible that jointCollection passed as parameter to the method was actually retrieved using different session, not the one that is passed as first arg? that's the only way I see it can ignore this null assignment

[jasonlaw]

It is quite impossible since I only maintain in one single session. Btw, just to note that the problem is inconsistent, sometime it works, but not all the time. I have rewrote my method to use the NonQuery execution, and it is more stable now. Just my thought, could it be the reference is not being loaded (since never call) and we are setting null for update, hence the dirty flag is skipped?

[rivantsov]

set bkpoint on first SaveChanges, stop there and inspect inside session, RecordsChanged list, there must be several invoices there in modified status, verify it, look at their prop values.

[jasonlaw]

Strange, I can't reproduce the error after setting the breakpoint. However, I see another issue which is quite constant.

After creating a new external party bill, it needs to be assigned to our system invoice.

From the debug, we can see the new bill and 2 modified invoices, that is correct.

By checking one of the invoice, we can see the bill is assigned correctly

However, after checking in the database, the value is not updated, which remain Null.

In the log file also can't find the update command. Log.txt

[jasonlaw]

I have tried with the following NonQuery but getting error, am I doing something wrong?

 var ids = invoices.Select(x => x.InvoiceNo).ToList();
            var updateQuery = session.EntitySet<IBillingInvoice>()
                                     .Where(x => ids.Contains(x.InvoiceNo))
                                     .Select(x => new { x.InvoiceNo, BillplzBill = bill });

            session.ScheduleUpdate<IBillingInvoice>(updateQuery);

Vita.Data.Linq.Translation.LinqTranslationException: Linq to SQL translation failed, invalid expression: Failed to find DB type for linq parameter of type VIQCore.Community.IBillplzBill
 ---> System.Exception: Failed to find DB type for linq parameter of type VIQCore.Community.IBillplzBill
   at Vita.Entities.Util.Throw(String message, Object[] args) in C:\JSL\VIQCore\2020Net\vita\src\1.Framework\Vita\2.Operations\StaticHelpers\Util.cs:line 21
   at Vita.Entities.Util.Check(Boolean condition, String message, Object[] args) in C:\JSL\VIQCore\2020Net\vita\src\1.Framework\Vita\2.Operations\StaticHelpers\Util.cs:line 25
   at Vita.Data.Driver.DbLinqSqlBuilder.CreateSqlPlaceHolder(ExternalValueExpression extValue) in C:\JSL\VIQCore\2020Net\vita\src\1.Framework\Vita\4.Internals\Data\Driver\SQL\DbLinqSqlBuilder_PlaceHolders.cs:line 32
   at Vita.Data.Driver.DbLinqSqlBuilder.BuildSqlForSqlExpression(SqlExpression expr) in C:\JSL\VIQCore\2020Net\vita\src\1.Framework\Vita\4.Internals\Data\Driver\SQL\DbLinqSqlBuilder.cs:line 186
   at Vita.Data.Driver.DbLinqSqlBuilder.BuildLinqExpressionSql(Expression expr) in C:\JSL\VIQCore\2020Net\vita\src\1.Framework\Vita\4.Internals\Data\Driver\SQL\DbLinqSqlBuilder.cs:line 116
   at Vita.Data.Driver.DbLinqNonQuerySqlBuilder.BuildLinqUpdateSimple() in C:\JSL\VIQCore\2020Net\vita\src\1.Framework\Vita\4.Internals\Data\Driver\SQL\DbLinqNonQuerySqlBuilder.cs:line 84
   at Vita.Data.Driver.DbLinqNonQuerySqlBuilder.BuildLinqNonQuerySql() in C:\JSL\VIQCore\2020Net\vita\src\1.Framework\Vita\4.Internals\Data\Driver\SQL\DbLinqNonQuerySqlBuilder.cs:line 38
   at Vita.Data.Linq.LinqEngine.TranslateNonQuery(DynamicLinqCommand command) in C:\JSL\VIQCore\2020Net\vita\src\1.Framework\Vita\4.Internals\Data\Linq\LinqEngine_NonQuery.cs:line 72
   at Vita.Data.Linq.LinqEngine.Translate(LinqCommand command) in C:\JSL\VIQCore\2020Net\vita\src\1.Framework\Vita\4.Internals\Data\Linq\LinqEngine.cs:line 58
   --- End of inner exception stack trace ---
   at Vita.Data.Linq.LinqEngine.Translate(LinqCommand command) in C:\JSL\VIQCore\2020Net\vita\src\1.Framework\Vita\4.Internals\Data\Linq\LinqEngine.cs:line 64
   at Vita.Data.Sql.SqlFactory.GetLinqSql(LinqCommand command) in C:\JSL\VIQCore\2020Net\vita\src\1.Framework\Vita\4.Internals\Data\Sql\SqlFactory.cs:line 38
   at Vita.Data.Runtime.Database.ExecuteLinqNonQuery(EntitySession session, LinqCommand command, DataConnection conn) in C:\JSL\VIQCore\2020Net\vita\src\1.Framework\Vita\4.Internals\Data\Data.Runtime\Database.cs:line 64
   at Vita.Data.Runtime.Database.ExecuteScheduledCommands(DataConnection conn, EntitySession session, IList`1 commands) in C:\JSL\VIQCore\2020Net\vita\src\1.Framework\Vita\4.Internals\Data\Data.Runtime\Database.cs:line 176
   at Vita.Data.Runtime.Database.SaveChangesNoBatch(DbUpdateSet updateSet) in C:\JSL\VIQCore\2020Net\vita\src\1.Framework\Vita\4.Internals\Data\Data.Runtime\Database.cs:line 140
   at Vita.Data.Runtime.Database.SaveChanges(EntitySession session) in C:\JSL\VIQCore\2020Net\vita\src\1.Framework\Vita\4.Internals\Data\Data.Runtime\Database.cs:line 82
   at Vita.Data.Runtime.DataSource.SaveChanges(EntitySession session) in C:\JSL\VIQCore\2020Net\vita\src\1.Framework\Vita\4.Internals\Data\Data.Runtime\DataAccessService\DataSource.cs:line 40
   at Vita.Entities.Runtime.EntitySession.SubmitChanges() in C:\JSL\VIQCore\2020Net\vita\src\1.Framework\Vita\4.Internals\Entities.Runtime\EntitySession.cs:line 202
   at Vita.Entities.Runtime.EntitySession.SaveChanges() in C:\JSL\VIQCore\2020Net\vita\src\1.Framework\Vita\4.Internals\Entities.Runtime\EntitySession.cs:line 185
   at VIQCore.Community.BillingModule.PaymentViaGatewayAsync(OperationContext context, List`1 invoices) in C:\JSL\VIQCore\2021Net\VIQCommunityNET\VIQCore.Community\Modules\Billing\BillingModule.cs:line 87
   at VIQCore.Community.BillingController.InvoicePaymentAsync(String invoices) in C:\JSL\VIQCore\2021Net\VIQCommunityNET\VIQCore.Community\Controllers\BillingController.cs:line 203
   at lambda_method377(Closure , Object )
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.Policy.AuthorizationMiddlewareResultHandler.HandleAsync(RequestDelegate next, HttpContext context, AuthorizationPolicy policy, PolicyAuthorizationResult authorizeResult)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Aether.Web.AetherWebMiddleware.InvokeAsync(HttpContext context, IHttpContextService _) in C:\JSL\Aether\AetherAll\Aether.Net\Web\AetherWebMiddleware.cs:line 60
Exception data (Vita.Data.Linq.Translation.LinqTranslationException): 
  LinqExpression = (@P0, @P1) => EntitySet<IBillingInvoice>.Where(x => @P0.Contains(x.InvoiceNo)).Select(x => new <>f__AnonymousType5`2(InvoiceNo = x.InvoiceNo, BillplzBill = @P1)) 

[jasonlaw]

Alright, after changing this it works now. So the issue still remain with the for loop assignment.

 .Select(x => new { x.InvoiceNo, BillplzBill_Id = bill.Id });

[rivantsov]

try removing this line inside the loop: bill.Invoices.Add(.. this list should be maintained/refreshed automatically by the system

[jasonlaw]

Close this issue for now since I have changed the way of method and it is working fine. Will keep an eye on such scenario and re-open again when have more solid reproduction. Thanks!