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:
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 Child
ren 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 Child
ren 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:
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:
Microsoft.EntityFrameworkCore.SqlServer
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));
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.