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:

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

• sql-server
• asp-core
• entity-framework-core
• -8.0
• unique-constraint

• 1 This might be solvable by (1) picking the updates apart: Clear flags + Save, Set single flag + Save again, and (2) wrapping it all together in a TransactionScope to ensure overall consistency. Example here from a somewhat similar scenario: stackoverflow/a/66978976/1220550 – Peter B Commented Nov 20, 2024 at 19:28
• We are trying to avoid that as we’re going for a more DDD approach where DB concerns are not limiting our domain model. – Seb Commented Nov 20, 2024 at 19:31

2 Answers 2

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/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.

I have ended up creating a few extensions to the DbCommand object, to check if re-ordering is necessary, and to do the actual re-ordering:

public static class DbCommandExtensions
{
    public static void ReorderUpdateStatementsIfNecessaryFor<T>(this DbCommand command, string propertyName)
    {
        if (command.ContainsUpdatesFor<T>(propertyName))
        {
            command.ReorderUpdateStatementsFor<T>(propertyName);
        }
    }

    private static bool ContainsUpdatesFor<T>(this DbCommand command, string propertyName)
    {
        var updateMyEntityRegex = new Regex(
            $@"UPDATE\s+(?:\[\w+\]\.)?\[?{typeof(T).Name}\]?[^\;]*SET\s+\[?(?:{propertyName})\]?\s*=",
            RegexOptions.IgnoreCase | RegexOptions.Compiled,
            TimeSpan.FromMilliseconds(100)
        );
        return updateMyEntityRegex.IsMatch(command.CommandText);
    }

    private static void ReorderUpdateStatementsFor<T>(this DbCommand command, string propertyName)
    {
        var updateRegex = new Regex(
            $@"UPDATE\s+(?:\[\w+\]\.)?\[?{typeof(T).Name}\]?[^\;]*SET\s+\[?{propertyName}\]?\s*=\s*(@[\w\d]+)[^\;]*;",
            RegexOptions.IgnoreCase,
            TimeSpan.FromMilliseconds(100)
        );
        var matches = updateRegex.Matches(command.CommandText);
        var setToFalse = new List<string>();
        var setToTrue = new List<string>();

        foreach (Match match in matches)
        {
            var updateStatement = match.Value;
            var parameterName = match.Groups[1].Value;

            if (command.Parameters.Contains(parameterName))
            {
                var parameter = command.Parameters[parameterName];

                if (parameter.Value is bool isSelected)
                {
                    if (!isSelected)
                    {
                        setToFalse.Add(updateStatement);
                    }
                    else
                    {
                        setToTrue.Add(updateStatement);
                    }
                }
                else
                {
                    // If the parameter is missing or its value is not a bool, consider it false
                    setToFalse.Add(updateStatement);
                }
            }
            else
            {
                // If the parameter is not found, treat it as false
                setToFalse.Add(updateStatement);
            }
        }

        // Reassemble the command text, replacing the matched UPDATE statements
        var reorderedUpdates = setToFalse.Concat(setToTrue).ToList();
        var updateIndex = 0;

        // Replace each match in order with the corresponding reordered update
        command.CommandText = updateRegex.Replace(
            command.CommandText,
            match => updateIndex < reorderedUpdates.Count
                ? reorderedUpdates[updateIndex++]
                : match.Value
        );
    }
}

It is then used in an interceptor this way:

public class MyEntityIsDefaultInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result
    )
    {
        command.ReorderUpdateStatementsIfNecessaryFor<MyEntity>(nameof(MyEntity.IsDefault));
        return base.ReaderExecuting(command, eventData, result);
    }

    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = new()
    )
    {
        command.ReorderUpdateStatementsIfNecessaryFor<MyEntity>(nameof(MyEntity.IsDefault));
        return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
    }
}