C
C#•2y ago
kommissar

Entity Framework - DbUpdateConcurrencyException due to a trigger on the table

I've been dealing with a problem for a very long time, and I've got no idea how to fix it. I'm a bit of a noob at Entity Framework, so any help appreciated. 
Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
After a very long period of trial and error, I found that the table has a trigger on it, which must update some entities. The result is the above exception. I have no idea how to fix this, my code is extremely simple. 
public async Task<Response<bool>> Handle(UpdateLastOperationCommand request, CancellationToken cancellationToken)
{
    var routeCard = await _myDbContext.RouteCards.FirstOrDefaultAsync(n => n.RouteNo == request.RouteNo);
    var routeOperations =
        await _myDbContext.RouteCardOps.Where(n => n.RouteNo == request.RouteNo).ToListAsync();
    var ordered = routeOperations.OrderBy(n => n.Seq);
    var lastOp = ordered.LastOrDefault();

    if (lastOp == null)
    {
        return new Response<bool>() { Succeeded = true, Data = false, Message = "No operation to update" };
    }

    routeCard.QtyFinished = lastOp.QtyComplete;
    if (routeCard.QtyFinished >= routeCard.BatchQty)
    {
        Log.Information($"RouteCard should be marked as complete");
        routeCard.RouteStatus = "CMP";
        routeCard.StatusDate = DateTime.Now;
        routeCard.CompletedDate = DateTime.Now;
        routeCard.SignOff = lastOp.SignOff;
        routeCard.SignOffOn = lastOp.SignOffOn;
    }

    await _myDbContext.SaveChangesAsync();
    Log.Information($"Successfully updated route card {request.RouteNo}");

    return new Response<bool>() { Succeeded = true, Data = true };
}
public async Task<Response<bool>> Handle(UpdateLastOperationCommand request, CancellationToken cancellationToken)
{
    var routeCard = await _myDbContext.RouteCards.FirstOrDefaultAsync(n => n.RouteNo == request.RouteNo);
    var routeOperations =
        await _myDbContext.RouteCardOps.Where(n => n.RouteNo == request.RouteNo).ToListAsync();
    var ordered = routeOperations.OrderBy(n => n.Seq);
    var lastOp = ordered.LastOrDefault();

    if (lastOp == null)
    {
        return new Response<bool>() { Succeeded = true, Data = false, Message = "No operation to update" };
    }

    routeCard.QtyFinished = lastOp.QtyComplete;
    if (routeCard.QtyFinished >= routeCard.BatchQty)
    {
        Log.Information($"RouteCard should be marked as complete");
        routeCard.RouteStatus = "CMP";
        routeCard.StatusDate = DateTime.Now;
        routeCard.CompletedDate = DateTime.Now;
        routeCard.SignOff = lastOp.SignOff;
        routeCard.SignOffOn = lastOp.SignOffOn;
    }

    await _myDbContext.SaveChangesAsync();
    Log.Information($"Successfully updated route card {request.RouteNo}");

    return new Response<bool>() { Succeeded = true, Data = true };
}
Any help would be appreciated.
11 Replies
FusedQyou
FusedQyou•2y ago
My guess is that when if (routeCard.QtyFinished >= routeCard.BatchQty) returns false, this error appears
kommissar
kommissarOP•2y ago
actually it's only when it enters the if statement.
FusedQyou
FusedQyou•2y ago
await _myDbContext.SaveChangesAsync(); might have expected atleast one change, but there were none
kommissar
kommissarOP•2y ago
If it does not enter the if statement, it's always fine.
FusedQyou
FusedQyou•2y ago
Hmmmm, I'm not sure Maybe you are assigning the same values to it, somehow
kommissar
kommissarOP•2y ago
nope, definitely not. Checked numerous times.
FusedQyou
FusedQyou•2y ago
Are there actual values on StatusDate that can represent DateTime.Now? This code looks fine to me, I have no clue
kommissar
kommissarOP•2y ago
kommissar
kommissarOP•2y ago
Thing is when I remove the triggers it works
FusedQyou
FusedQyou•2y ago
What trigger?
kommissar
kommissarOP•2y ago
there is a trigger on the table that updates a bunch of stuff, but not this particular table 
USE [Dev]
GO
/****** Object:  Trigger [dbo].[tr_route_cards_update]    Script Date: 06/02/2023 09:03:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_route_cards_update] ON [dbo].[route_cards] 
AFTER UPDATE 
AS
BEGIN
    IF UPDATE(part_num) OR UPDATE(issue) OR UPDATE(batch_qty) OR UPDATE(qty_finished) OR UPDATE(qty_rejected) OR 
        UPDATE(route_status) OR UPDATE(inspected) OR UPDATE(sign_off)
    BEGIN
        DECLARE @old_qty_rejected NUMERIC(19,7);
        DECLARE @new_qty_finished NUMERIC(19,7);
        DECLARE @new_qty_rejected NUMERIC(19,7);
        DECLARE @new_batch_qty INTEGER;
        DECLARE @new_part_num NVARCHAR(36);
        DECLARE @new_part_issue NVARCHAR(10);
        DECLARE @route_date DATETIME;
        DECLARE @contract_no INTEGER;
        DECLARE @top_level_part NVARCHAR(36);
        DECLARE @top_level_issue NVARCHAR(10);

        SELECT    @new_qty_finished = COALESCE(qty_finished, 0)
                ,@new_batch_qty = batch_qty
                ,@new_part_num = part_num
                ,@new_part_issue = issue
                ,@top_level_part = top_level_part
                ,@top_level_issue = top_level_issue
                ,@contract_no = contract_no
        FROM inserted;    
        SET @new_qty_rejected = 0; 

        -- part last order details update
        UPDATE    parts 
        SET        last_order_date = GETDATE()
                ,last_order_qty = @new_qty_finished - @new_qty_rejected
                ,lead_time = DATEDIFF(dd,  @route_date, GETDATE())
        WHERE    parts.part_num = @new_part_num 
        AND        parts.issue = @new_part_issue;

        IF @contract_no is not null AND @contract_no > 0 AND @top_level_part = @new_part_num AND @top_level_issue = @new_part_issue
            execute sp_contract_update_qty_info @contract_no;
    END;
END;
USE [Dev]
GO
/****** Object:  Trigger [dbo].[tr_route_cards_update]    Script Date: 06/02/2023 09:03:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_route_cards_update] ON [dbo].[route_cards] 
AFTER UPDATE 
AS
BEGIN
    IF UPDATE(part_num) OR UPDATE(issue) OR UPDATE(batch_qty) OR UPDATE(qty_finished) OR UPDATE(qty_rejected) OR 
        UPDATE(route_status) OR UPDATE(inspected) OR UPDATE(sign_off)
    BEGIN
        DECLARE @old_qty_rejected NUMERIC(19,7);
        DECLARE @new_qty_finished NUMERIC(19,7);
        DECLARE @new_qty_rejected NUMERIC(19,7);
        DECLARE @new_batch_qty INTEGER;
        DECLARE @new_part_num NVARCHAR(36);
        DECLARE @new_part_issue NVARCHAR(10);
        DECLARE @route_date DATETIME;
        DECLARE @contract_no INTEGER;
        DECLARE @top_level_part NVARCHAR(36);
        DECLARE @top_level_issue NVARCHAR(10);

        SELECT    @new_qty_finished = COALESCE(qty_finished, 0)
                ,@new_batch_qty = batch_qty
                ,@new_part_num = part_num
                ,@new_part_issue = issue
                ,@top_level_part = top_level_part
                ,@top_level_issue = top_level_issue
                ,@contract_no = contract_no
        FROM inserted;    
        SET @new_qty_rejected = 0; 

        -- part last order details update
        UPDATE    parts 
        SET        last_order_date = GETDATE()
                ,last_order_qty = @new_qty_finished - @new_qty_rejected
                ,lead_time = DATEDIFF(dd,  @route_date, GETDATE())
        WHERE    parts.part_num = @new_part_num 
        AND        parts.issue = @new_part_issue;

        IF @contract_no is not null AND @contract_no > 0 AND @top_level_part = @new_part_num AND @top_level_issue = @new_part_issue
            execute sp_contract_update_qty_info @contract_no;
    END;
END;
Want results from more Discord servers?
Add your server