EF Core index and soft delete entity.

[maliming]

b.HasIndex(e => e.Name).IsUnique();

If the entity supports soft deletion, there may be a problem.
When you delete an entity and continue adding the same entity, the index will prevent the addition.

HasFilter seems to work, but needs to continue investigation.
b.HasIndex(e => e.Name).IsUnique().HasFilter("[IsDeleted] != 1");

[maliming]

HasFilter requires sql statement may have database compatibility issues.

@hikalkan
Should we provide a filter sql statement for common databases?
Or is there any other solution?

[hikalkan]

Normally, developer should care if the entity is soft delete and apply index based on it, like b.HasIndex(e => new { e.Name, e.IsDeleted }).IsUnique();

However, this is also not the final solution. Think that you have deleted an entity named "Foo", then created another entity with name "Foo", then you can not delete it since there will be two deleted "Foo" in this case.

I didn't know b.HasIndex(e => e.Name).IsUnique().HasFilter("[IsDeleted] != 1"); usage, but it seems a good solution at least for some databases. However, I think this is not ABP's job and developer should care about it. We may create an extension method for HasFilter("[IsDeleted] != 1") at most and document it.

[maliming]

Thank you for your explanation.

[wboebel]

A possible alternative to the IsDeleted issue is for IsDeleted to not be saved to the database and only save DeletionTime. IsDeleted could be a property that checks if DeletionTime is null => return !this.DeletionTime.HasValue.

Then you could do a unique index on { e.Name, e.DeletionTime }, cover both the Name and DeletionTime in the index, it would enforce that only one e.Name exists per value which is not deleted, and allows you to accumulate multiple soft deleted records as each would have a different time.

But of course this would change the way the base class is implemented and how the data filter works.

I'm considering two indexes instead as a workaround to avoid making those changes:

1. Unique index: e.Name, e.DeletionTime - enforce the unique constraint
2. Non-unique index: e.Name, e.IsDeleted - for query purposes as soft delete filter is based on IsDeleted.