Stale index cache after row deletion causes uniqueness constraint violations

[sardar97]

Delta.EF version: 6.4.2 EF Core version: 9.0.6 Database provider: SQL Server 2022 .NET SDK: 9.0.x

Current behavior

When a row is hard‑deleted from the database inside the same DbContext transaction, DeltaCache does not invalidate the cached 304 token for the unique index (Email, Role) on the Profile table. Subsequent attempts to insert a new row with the same (Email, Role) pair immediately hit the stale cache and EF Core raises a UNIQUE KEY violation, even though the database no longer contains the original row.

Expected behavior

After DELETE completes, the next GET/HEAD for that resource should detect the change via the tracking table and either:

1. Emit a new ETag/304 token that reflects the actual state, or
2. Skip the cache altogether until change tracking reports the delete.

In either case the cache must not serve an outdated entry that makes the index appear occupied.

Minimal reproduction

1. Configure DeltaCache as per README (AddDeltaCache etc.) with change‑tracking enabled on table Profile.

2. Ensure a filtered unique index (Email, Role) exists and create a row, e.g. ( '[email protected]', 0 ).

3. DELETE the Profile row using EF Core:

   var profile = await db.Profiles.SingleAsync(p => p.Email == "[email protected]");
   db.Profiles.Remove(profile);
   await db.SaveChangesAsync();
   

4. In the same service lifetime / shortly after, create a replacement row with the same email:

   db.Profiles.Add(new Profile { Email = "[email protected]", Role = 0, /* other props */ });
   await db.SaveChangesAsync(); // <-- fails
   

5. Observe SqlException:

   Cannot insert duplicate key row in object 'dbo.Profile' with unique index 'IX_Profile_Email_Role'. The duplicate key value is ([email protected], 0).
   

6. Query the table:

   SELECT * FROM dbo.Profile WHERE Email = '[email protected]'; -- returns 0 rows
   

Analysis

DeltaCache seems to cache only the entity payload keyed by the primary key, but not its dependent unique‑index keys. When the row is deleted the corresponding index entry disappears; however, the 304 token is still served, so EF re‑hydrates the stale entity which keeps the old index values. Effectively the cache acts as a “zombie row”.

Possible solutions

• Invalidate cache entries not only when sys.dm_db_index_usage_stats sees an UPDATE, but also when change tracking reports a DELETE.
• Record the set of unique keys per entity and evict them as part of delete processing.
• Provide an opt‑out switch, e.g. DeltaCacheOptions.EvictOnDelete = true.

Workaround

Disable DeltaCache for the affected controller method or explicitly clear cache after hard deletes:

await deltaCache.EvictAsync<Profile>(profileId);

…but this defeats the purpose of automatic caching.

Thank you for your great work!

[SimonCropp]

@sardar97 thanks for the detailed explanation. would it be possible to get a failing unit test that illustrates this scenario

[sardar97]

@SimonCropp thanks a lot for the quick turnaround and for offering to look into this.

I’m afraid I’m not able to supply a failing unit test or a standalone repro right now. I don’t have any prior experience writing automated tests for EF Core/DeltaCache and I’m currently on a tight deadline, so I don’t have the bandwidth to set one up. I realise this makes it harder for you to diagnose the issue and I apologise for the inconvenience.

Thanks again for your support—and sorry I can’t put together a test at the moment.

[SimonCropp]

does your service account user have the VIEW SERVER STATE permission? https://github.com/SimonCropp/Delta/blob/main/docs/sqlserver.md#implementation

[sardar97]

Thanks for the follow-up. I just ran: SELECT HAS_PERMS_BY_NAME(NULL, NULL, 'VIEW SERVER STATE') AS HasVss; …and it returned 0, so it looks like the service account does not have the VIEW SERVER STATE permission. That explains why Delta wasn’t detecting the DELETE and kept serving the stale cache.

I’ll arrange to have the permission granted and will retest the scenario. Will report back once that’s done.

Appreciate the guidance—this was really helpful!