c# Soft Delete and Unique constraints

Question

Hopefully this is a quick one. I am using global query filters to enable a soft delete solution (basically I have added a Deleted property to all my entities). In most cases this is fine, but I have just run into an issue. One of my entities was soft deleted, so it exists in the table but with Deleted set to false and someone has tried to create another entity with the same name.

The database has thrown an error stating that a unique constraint has been violated, which refers to this:

modelBuilder.Entity<Brand>().HasIndex(m => m.Name).IsUnique();

As you can see, I don't want anyone to be able to create a brand with the same name, but if that brand has been soft deleted, then it should allow me. Is it possible to set that up? Like a conditional constraint?

Here is a list of my unique constraints for context:

        modelBuilder.Entity<Attribute>().HasIndex(m => new { m.Name, m.CriterionId }).IsUnique();
        modelBuilder.Entity<Brand>().HasIndex(m => m.Name).IsUnique();
        modelBuilder.Entity<Contact>().HasIndex(m => new { m.BrandId, m.Email }).IsUnique();
        modelBuilder.Entity<Criterion>().HasIndex(m => new { m.Name, m.CategoryId }).IsUnique();
        modelBuilder.Entity<Field>().HasIndex(m => new { m.CategoryId, m.Name, m.IsSpecification }).IsUnique();
        modelBuilder.Entity<FieldMap>().HasIndex(m => new { m.FeedId, m.FieldId }).IsUnique();
        modelBuilder.Entity<BrandCategory>().HasIndex(m => new { m.Id, m.BrandId, m.CategoryId }).IsUnique();
        modelBuilder.Entity<BrandUser>().HasIndex(m => new { m.Id, m.BrandId, m.UserId }).IsUnique();
        modelBuilder.Entity<CategoryGroup>().HasIndex(m => new { m.Id, m.CategoryId, m.GroupId }).IsUnique();
        modelBuilder.Entity<TheatrePlan>().HasIndex(m => new { m.Id, m.TheatreId, m.PlanId }).IsUnique();
        modelBuilder.Entity<UserCategoryInterest>().HasIndex(m => new { m.Id, m.UserId, m.CategoryId }).IsUnique();
        modelBuilder.Entity<UserPlanInterest>().HasIndex(m => new { m.Id, m.UserId, m.PlanId }).IsUnique();
        modelBuilder.Entity<UserTheatreInterest>().HasIndex(m => new { m.Id, m.UserId, m.TheatreId }).IsUnique();

*One of my entities was soft deleted, so it exists in the table but with Deleted set to false* - huh? Your deleted entity has deleted=false? — Caius Jard, Jan 21 '21 at 14:37
It sounds like you've made a poor choice of primary key, or you should restore the deleted entity — Caius Jard, Jan 21 '21 at 14:39
see this https://stackoverflow.com/questions/3492485/mysql-with-soft-deletion-unique-key-and-foreign-key-constraints — milo, Jan 21 '21 at 14:40
Perhaps when you soft-delete something, you could rename the brand in an algorithmic fashion, say from _MyBrand_ to _MyBrand-Deleted_. If you have an _Undelete_ capability, then you could check to see if the Unique rule is still good, and if it is, strip of the "-Deleted" part (and, if not, signal an error) — Flydog57, Jan 21 '21 at 14:56

Nantharupan · Answer 1 · 2021-01-21T19:02:36.147

1

You could try something similar to the following.

modelBuilder.Entity<Brand>().HasIndex(p=> new {m => m.Name, m=>m.Deleted}).IsUnique().HasFilter("[Deleted = 0]");

edited Jan 21 '21 at 19:02

answered Jan 21 '21 at 15:04

Nantharupan

594
3
15

It fails :( with the error Inccorect syntax near ';' on this line `CREATE UNIQUE INDEX [IX_UserTheatreInterests_Id_UserId_TheatreId] ON [UserTheatreInterests] ([Id], [UserId], [TheatreId]) WHERE [Deleted = 0];` – r3plica Jan 21 '21 at 15:09
did you try with all entities? or only one? Try with one first and see – Nantharupan Jan 21 '21 at 15:11
I tried with them all, that is the first one it creates. I will try with them individually to see if I can figure out what the issue is – r3plica Jan 21 '21 at 15:17
1

Did you mean `"[Deleted] = 0"` – Caius Jard Jan 21 '21 at 15:24
@r3plica I fixed a mistake in my suggestion. I hope you are using MS SQL server. – Nantharupan Jan 21 '21 at 19:03
You still have a typo, it's as @CaiusJard said – r3plica Mar 05 '21 at 10:55

score 1 · Accepted Answer · answered Jan 21 '21 at 18:48

With a normal index, that is not possible.

You could try using an index filter (see https://learn.microsoft.com/de-de/ef/core/modeling/indexes?tabs=data-annotations#index-filter) but that ONLY works for MS SQLServer as other providers such as MySQL don't support partial indexing (see Workaround in mysql for partial Index or filtered Index?), so it depends on your database provider.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Brand>()
        .HasIndex(b => b.Name)
        .IsUnique()
        .HasFilter("[Deleted] IS NOT NULL");
}

c# Soft Delete and Unique constraints

2 Answers2