Allow to change the order of updates when using a filtered unique index

[sebdesalvador]

The question

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 correclty 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:

Id	UserId	IsDefault
1	5	0
2	5	1
3	5	0

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 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:

Id	UserId	IsDefault
1	5	1
2	5	1
3	5	0

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

[ajcvickers]

@AndriySvyryd This is a consequence of us not being able to parse the filter on the index. We did #28923 a couple of years ago to avoid cycles, but I don't see anything else tracking further changes to this. Do we have anything on the backlog that I missed?

[AndriySvyryd]

I don't think this is tracked anywhere else. We'd need to add API to provide a callback that determines what dependency should be broken in a cycle.

[roji]

Another possible answer here (for today) is to manually break the batch into two SaveChanges. This does cause an additional roundtrip, but anything else here seems like it could be too complicated/convoluted...

[rvdginste]

Another possible answer here (for today) is to manually break the batch into two SaveChanges. This does cause an additional roundtrip, but anything else here seems like it could be too complicated/convoluted...

This reminds me of a similar problem you can get with a unique constraint that does not have a filter. When “reusing” the same unique key, you want to enforce the ‘delete’ happening before the ‘insert’. On nhibernate I used the technique mentioned by @roji . But should I interpret comment from @ajcvickers that efc enforces correct ordering automatically in that case?

[roji]

@rvdginste yes, it does.