Entity Framework is not consistently generating the correct UPDATE statement when updating two entities

A lot can go wrong when attempting to update two records in accordance to a unique constraint where one must surrender a value such as "IsDefault" before the other can reserve it. Not only is there the issue of concurrent updates by other systems/sessions, but also that without a direct relation we cannot guarantee the updates will occur in a predictable order.

Option 1: Handle the exception. As there are bound to be scenarios outside of our control that could result in an error, one option is to handle the error and either retry or bring it to the user's attention to refresh and retry the operation.

Option 2: Explicitly commit the changes separately to ensure they are done in order.

CustomerPO existingPO = (from item in dbContext.CustomerPO 
    where (item.PurchaseOrderID == purchaseOrder.PurchaseOrderID) 
    select item).FirstOrDefault();

var currentDefault = (from item in dbContext.CustomerPO
    where item.IsDefault && item.CustomerID == existingPO.CustomerID
    select item).FirstOrDefault();

using var transaction = dbContext.Database.BeginTransaction();

if (currentDefault != null && currentDefault.PurchaseOrderID != existingPO.PurchaseOrderID)
{
    currentDefault.IsDefault = false;
    dbContext.SaveChanges();
}

dbContext.Entry(existingPO).CurrentValues.SetValues(purchaseOrder);
dbContext.SaveChanges();
transaction.Commit();

This isn't ideal given the two saves, and we'd need a transaction to ensure that both changes are committed or rolled back together. Otherwise we could have a situation where we remove an IsDefault state without successfully setting the new record.

Option 3: Manage the IsDefault with a trigger. I recently needed to do something similar for saved searches where I was recording the last selected search. As a user selected a saved search criteria I would update it to mark it as selected so the next time they returned it would be the default.

ALTER TRIGGER [dto].[CustomersSingleIsDefault] 
   ON  [dro].[Customers] 
   AFTER INSERT, UPDATE
AS 
BEGIN

    SET NOCOUNT ON;

    DECLARE @isSelected AS BIT,
        @customerId AS INT,
        @typeName AS VARCHAR(500);

    -- Don't allow this trigger to trip recursively as we will be updating rows.
    IF TRIGGER_NESTLEVEL(@@PROCID) > 1
        RETURN;

    IF NOT UPDATE(IsDefault)
        RETURN;

    SELECT @customerId = CustomerId, @isDefault = IsDefault 
    FROM INSERTED;

    IF @isDefault = 1
    BEGIN
        UPDATE [dto].Customers SET IsDefault = 0 
        WHERE CustomerId = @customerId AND IsDefault = 1; 
    END;

END

The advantage of the trigger is that it runs for any process that alters the table to ensure that one record is selected. For EF Core entities you need to mark the customer entity to tell EF that it has a trigger associated to it. Typically with:

builder.ToTable(tb => tb.UseSqlOutputClause(false));

The caveat is adding a trigger to the table and potential performance implications of that.

One other detail to check over given you have a unique constraint involved with this IsDefault flag is the potential to have situations resulting in multiple non-default (IsDefault = false) rows attempting to be created. This may happen if code you assume should find a non-default row and mark it as default isn't working as expected, and instead falls through to inserting a new "IsDefault=true" row. I had recently had to track down a bug in a system similar to this with a soft-delete where the IsActive flag was made part of a unique constraint. The system was working for the most part until you went to re-activate a previously inactive row. A bug saw it not find the inactive row to reactivate and inserted a new active one. (no error) However later down the road when a user goes to deactivate that new item, the constraint fails due to now having two inactive rows. It was an annoying error given it only manifested long after the initial bug responsible created the invalid data scenario. (inserting instead of re-activating)