entity framework - 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();

question from:https://stackoverflow.com/questions/65829655/c-sharp-soft-delete-and-unique-constraints

Answer 1

With a normal index, that is not possible.

You could try using an index filter (see https://docs.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");
}