Problem after Remove() with cascade

[moozzyk]

Ported from: https://efcache.codeplex.com/workitem/18

REPORTED ON:
REPORTED BY:
Aug 29, 2015 at 11:26 AM
jsneeringer (contact)

Entity Framework - Database First, with cascade delete
The following code deletes a large number of records with cascade delete.

ElecF elecF = mVgF.ElecFs.Find(elecK);
if (elecF != null) {
	mVgF.ElecFs.Remove(elecF);
	mVgF.SaveChanges();
	EfCache.Cache.Purge();
	mVgF = new VgFinal(); 
}

Everything works fine if the L2 cache is not used, but when caching is done then the following code fails in SQL Server (EF seems to be happy) on the last SaveChanges() because of the foreign-key constraint that requires the HeaderK value to be in table HeaderF.

HeaderF headerF = (from h in mVgF.HeaderFs where h.ElecK == elecK & h.HeaderK == quesK select h).FirstOrDefault();
if (headerF == null) { // THIS BLOCK SHOULD BE EXECUTED, BECAUSE HeaderF SHOULD BE NULL.
	headerF = new HeaderF() { ElecK = elecK, HeaderK = quesK, RowType = (byte)quesK };
	mVgF.HeaderFs.Add(headerF);
	mVgF.SaveChanges();
}
RowF rowF = new RowF() { ElecK = elecK, Seq = seq, HeaderK = headerF.HeaderK, Text = "" };
mVgF.RowFs.Add(rowF);
mVgF.SaveChanges();

The items removed from the database by the Remove() in the first snippet actually get deleted from the SQL databasee. However, EF seems to think that at least one of the HeaderF records is still there, and I can actually see a value there when I set a breakpoint at "if (HeaderF == null)." It looks like the cascade is confusing things somehow. The HeaderF record is gone from SQL Server, however, so there is a violation of the foreign-key constraint when SQL tries to execute the INSERT.

Again, it works fine without the L2 cache, returning null for headerF.

moozzyk wrote Aug 30, 2015 at 10:17 PM [x]

Can you attach a minimal repro? You seem to be purging cache after the cascade delete so I am not exactly sure where EF would take the related items from. Without seeing the model, how the DbContext is being used and which queries might cache which results it is almost impossible to tell whether the behavior is a bug, a limitation or something else.
moozzyk wrote Aug 30, 2015 at 10:32 PM [x]

I think I know what's happening. I just looked at the code and remembered (yes, it's been a while since I saw this code the last time) that EfCache.Cache.Purge(); will not clean the cache completely. What it actually does is that it removes expired entries from the cache. So, in your case after you Purge the cache you will still have entries in the cache. Now, because you set up cascade on delete in the database EF may not invoke DELETE on items in the related entity sets if they are not tracked and the cache won't be invalidated. This may result in the discrepancy between the database and the cache and if you repeat a query for which results were cached you will get the result from the cache and not from the database. Try invalidating cache for the related entity set if you enabled cascade delete to remove stale results from the cache.
jsneeringer wrote Aug 31, 2015 at 6:39 AM [x]

Thank you so much for helping.

I'm not sure I understand your suggestion. Unless I misunderstand something, every entity is tracked, because all operations were done through EF. I did not expect the purge to be needed at all, but put it in because of this problem.

Are you suggesting that I walk the tree of entities underneath the ElecF record and delete each one individually? Is there a way to invalidate the entire cache? Are you saying that EF does not track cascade deletes?

Finally, I want to make sure you understand that the entity appears to exist, and then with no other operations on the database or in EF, it seems to be gone.

Thanks again for your help.