Linq Logical Delete with associations

Question

Hi,

I am trying to implement a logical delete senario across all my entities. All have an IsDeleted boolean property. I would also like to cascade the association deletes also. I started by adding a partial method for the entity to the datacontext.

partial void DeleteQuestion(Question instance)
{
    instance.IsDeleted = true;
    ExecuteDynamicUpdate(instance);
    foreach (var answer in instance.Answers)
    {
       DeleteAnswer(answer);
    }
}

This throws an error Incorrect syntax near the keyword 'WHERE' at ExecuteDynamicUpdate. Looking at sql profiler I can see the set clause is blank, it is not recording the IsDeleted = true; as a change. I checked the IsDeletedChanging event and it does get fired but I guess the change set has been built already by this time.

I also tried submitting the changes instead of calling ExecuteDynamicUpdate but this throws the exception The operation cannot be performed during a call to SubmitChanges.

I have seen an identical discussion here with the only resolution being to use stored procedures which I would rather not do.

So I thought lets ask the SO community and get a proper answer to the top of the Google results. There seems to be little on this topic.

Many thanks.

Answer 1

It appears you are calling DeleteQuestion directly in your code. You should not be doing this. This partial method is called as a result of a Delete action. Therefore, the instance being passed in your partial method has already been targeted for a Delete, which is why this method is being called by Linq to SQL. It looks like you are calling this method yourself and then attempting to delete the instance being passed as a parameter. This is not what you want to do.

Answer 2

Do you really need to persist the deleted state in the entire containment heirarchy?

Say you have a question "Q" and answers "A" and "B". At some point you safedelete "A". (State 1) "Q has B".

Now you safedelete "Q", expecting that when you undelete "Q" you will get (State 1). But safedelete has set "B" to deleted state, and undelete have set all children of "Q" to undeleted state, then you get "Q has A, B". Both safedelete and undelete operations have destroyed the state of children collection elements.

I call this concept "distructing editing", which means operations are irrevertible, and makes logical delete pretty much useless.

I would recommend using "non-descrtructing editing", which implies inference of IsDeleted state from containment hierarchy. If a parent IsDeleted, this means a child is considered deleted too.