I have a method that will Remove a set of entities.
I have a method that will AddOrUpdate a set of entities.
These methods are independently useful, but they have issues working together in entity framework.
The problem is that after removing a set of entities, for example (A,B,C,D), subsequent queries that resolve to one or more of those records always return cached garbage copies whose property values were nulled during the removal process. An intermediate DbContext.SaveChanges solves the issue, but introduces additional overhead and leaves the operation in a half-complete state, so it would also have to be wrapped in another transaction.
What's the best way to handle this.
- Should I avoid an API that has Remove and Add/Update operations altogether, instead opting for an up-front hybrid operation that determines which ones are actually being removed and which ones are sticking around to be updated? or
- Should I keep the two useful methods and just wrap the two steps in a transaction scope, so that I can save changes to the context immediately after the remove, allowing subsequent adds/updates to properly reflect their removal, while still have the ability to commit or rollback at the end (e.g. if the new permissions can/cannot be set)?
I don't want use lower-level operations such as turning off tracking or attaching/detaching entities.
Suppose the entities are permissions. Business logic dictates that I should use a logical two-step process to reset a user's permissions by first deleting any that I have permission to delete, followed immediately by trying to add/update any new permissions that I am allowed to assign.
When approaching this with two separate steps, I run into the problem as follows. The first problem I encounter is that immediately after removing a set of permission entities like (A,B,C,D), entity framework mangles the object properties, setting many of them to null (presumably to sever foreign key relationships). The problem is that because the entities still exist in the database, when trying to "add or update" a permission which still has a record in the database but has been removed in the context, EF always returns the cached/garbage copy of it. So although I've removed the entity... I can't actually determine, within that same context, whether I need to re-attach/update it or add a new entity outright. In other words, the framework returns an entity as though it exists (because it does still exist in the database) in spite of it being flagged as removed, but that entity object has garbage/null data, so that I can't even tell at that point whether it's safe to add a new one or I should try to "un-remove" the existing one.
It seems to me that such a remove/add-or-update pattern is simply not good for this kind of entity framework (or even ORMs in general). Instead, I'd have to determine, in a single up-front operation, whether any of the new permissions already exist, so I can selectively delete the ones that are going away, while updating the ones that are just being reassigned (a new access level, for example).
