Manage the order of updates when using a filtered unique index?

I have a filtered unique index defined as:

builder
    .HasIndex(e => new { e.UserId, e.IsDefault })
    .HasFilter($"[{nameof(MyEntity.IsDefault)}] = 1")
    .IsUnique()
    .HasDatabaseName($"UX_{nameof(MyEntity)}_{nameof(MyEntity.UserId)}_{nameof(MyEntity.IsDefault)}");

It translates into:

CREATE UNIQUE NONCLUSTERED INDEX [UX_MyEntity_UserId_IsDefault] 
ON [dbo].[MyEntity] ([UserId] ASC,
                     [IsDefault] ASC)
WHERE ([IsDefault] = (1))

Because I can only have 1 record with the flag IsDefault set to true, the order of updates is important, I first need to set non default records to false, and then set the default one. Since the EF Core doesn't really know the condition/filter, it doesn't order the updates correctly and I often get an error with the message

Cannot insert duplicate key row in object 'dbo.MyEntity' with unique index 'UX_MyEntity_UserId_IsDefault'

Example

In the database I have:

In the code, we have a Parent and a Child object, the Parent has a collection of Children and it goes like this:

var parent = await parentEfRepository.GetByIdAsync(5, cancellationToken);

var newDefaultChild = parent.Children.Where(c => ...);
newDefaultChild.SetAsDefault();

await context.SaveChangesAsync(cancellationToken);

The SetAsDefault() method contains the logic to set other Children as non default and the result is that only 1 Child is marked as being the default one.

When we get to SaveChanges(), EF Core generates a SQL script that contains the updates which look like this:

SET NOCOUNT ON;

UPDATE [MyEntity] 
SET [IsDefault] = @p0
OUTPUT INSERTED.[PeriodEnd], INSERTED.[PeriodStart]
WHERE [Id] = @p1;

UPDATE [MyEntity] 
SET [IsDefault] = @p2
OUTPUT INSERTED.[PeriodEnd], INSERTED.[PeriodStart]
WHERE [Id] = @p3;

Because it's trying to update record with ID 1 first, we would end up with the following:

And that's when the unique constraint kicks in and throws an error. I have started experimenting with interceptors but it's getting dirty, surely someone had this issue before me?

Thanks!

Provider and version information:

• EF Core version: 8.0.11
• Database provider: Microsoft.EntityFrameworkCore.SqlServer
• Target framework: .NET 8.0
• Operating system: Windows
• IDE: Rider

I had a similar requirement for a single selection that could be changed. The most reliable solution I could come up with was using a trigger on the DB. The code didn't need to bother clearing the flag, just setting it. In my case it was for saved searches where a default selection could be recorded. I avoid triggers, but for a scenario like this it was what made the most sense for reliability & simplicity.

Edit: The AFTER trigger won't work with the original unique constraint which wasn't something my scenario had. To support the unique constraint Trigger can be updated to an INSTEAD OF variant:

CREATE TRIGGER [dbo].[SearchesSingleRowIsSelected] 
   ON  [dbo].[Searches] 
   INSTEAD OF INSERT, UPDATE
AS 
BEGIN

    SET NOCOUNT ON;

    DECLARE @isSelected AS BIT,
        @searchId AS INT,
        @userId AS INT,
        @appId 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(IsSelected)
        RETURN;

    SELECT @searchId = SearchId, @isSelected = IsSelected, @userId = UserId, @appId = AppId, @typeName = TypeName 
    FROM INSERTED;

    IF @isSelected = 1
    BEGIN
        UPDATE [dbo].Searches SET IsSelected = 0 
        WHERE UserId = @userId AND AppId = @appId AND TypeName = @typeName AND IsSelected = 1 AND SearchId <> @searchId

        UPDATE [dbo].Searches SET IsSelected = 1 
        WHERE SearchId = @searchId
    END
    ELSE
    BEGIN
        UPDATE [dbo].Searches SET IsSelected = 0 
        WHERE SearchId = @searchId
    END;

END
GO

ALTER TABLE [dbo].[Searches] ENABLE TRIGGER [SearchesSingleRowIsSelected]
GO

This ensures the rows are updated in the correct order.

Then from the entity side it just needed to be configured to accommodate the trigger:

// example from EntityTypeConfiguration 
builder.ToTable(tb => tb.UseSqlOutputClause(false));

(https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.sqlserverentitytypeextensions.usesqloutputclause?view=efcore-8.0)

The benefit of the trigger approach was that it was applied regardless of what updated the row, and dead simple from setting the flag via EF without worrying about order of operations, etc.

Original trigger approach without filtered unique constraint:

CREATE TRIGGER [dbo].[SearchesSingleRowIsSelected] 
   ON  [dbo].[Searches] 
   AFTER INSERT, UPDATE
AS 
BEGIN

    SET NOCOUNT ON;

    DECLARE @isSelected AS BIT,
        @searchId AS INT,
        @userId AS INT,
        @appId 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(IsSelected)
        RETURN;

    SELECT @searchId = SearchId, @isSelected = IsSelected, @userId = UserId, @appId = AppId, @typeName = TypeName 
    FROM INSERTED;

    IF @isSelected = 1
    BEGIN
        UPDATE [dbo].Searches SET IsSelected = 0 
        WHERE UserId = @userId AND AppId = @appId AND TypeName = @typeName AND IsSelected = 1 AND SearchId <> @searchId
    END;

END
GO

ALTER TABLE [dbo].[Searches] ENABLE TRIGGER [SearchesSingleRowIsSelected]
GO

This would need to be modified if you wanted to enforce that one row must be selected. In my case you could have no selection.