Add extra database command with CommandTree interceptor RRS feed

  • Question

  • I am trying to implement an auditable datastore in Entity Framework. My intention is to keep a history of every record's state at any given point in time. This requires that I convert all delete statements to updates and all update statements to update + insert.

    I followed the TechEd 2014 EF6 soft delete session video for the basic setup of the interceptor, but I have come to a point where I am not sure how to proceed. I have valid cases for query, delete, and insert, but update is the tricky one.

    Here's the basic structure of the method:

    public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext)
            if (interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace)
                //other query interceptors
                var updateCommand = interceptionContext.OriginalResult as DbUpdateCommandTree;
                if (updateCommand != null)
                    //I modify the command to soft delete the current record
                    //(This is pseudo code to replace to verbose EF exp builder code)
                    var newClause = GetNewSoftDeleteClause(updateCommand);
                    interceptionContext.Result = GetUpdateCommandTree(updateCommand, newClause);
                    //Here is where I want to insert a new command into the tree
                    //and copy over the data to a new record

    As far as I can tell, one can modify the current Result within the TreeCreated method, but I cannot find a way to insert a new command into the context. As the interceptor appears to only deal with a single-row operation, I am beginning to think what I want to do is not possible within the TreeCreated method.

    Is there a way to accomplish what I want to do using interceptors without resorting to database triggers?

    This question has been taken verbatim from my question on StackOverflow ( as it has gotten very little traction in the last couple of months.

    Monday, August 11, 2014 5:16 PM

All replies

  • You can use Jimmy Bogards filter extension, and save a lot of headaches:

    Please mark as answer, if this was it. Visit my SQL Server Compact blog

    Tuesday, August 12, 2014 6:43 AM
  • This remains unresolved as it appears Erik to have misunderstood my issue. The problem is not with filtering, but with adding extra database commands using an interceptor.
    Thursday, August 14, 2014 2:07 PM
  • Hi Timothy,

    I would suggest that you could also add a comment under that video directly, then you might get an detail respondse soon.

    Friday, August 15, 2014 8:45 AM
  • Why not simply override saveChanges?

    Please mark as answer, if this was it. Visit my SQL Server Compact blog

    Friday, August 15, 2014 8:57 AM
  • I have worked with overriding SaveChanges in the past, but I'm not a huge fan of it. I know I could implement this within SaveChanges, but I would like to use interceptors if at all possible. I'm trying to determine if what I want to do is possible with interceptors.

    I added a comment to the video thread, so we'll see what comes of that.

    Friday, August 15, 2014 2:29 PM
  • Hello philrosenberg,

    >>This requires that I convert all delete statements to updates and all update statements to update + insert.

    With the session video, it is possible to convert a delete statement to update statement while I do think it is possible to convert one statement to two statements with CommandTree interceptor because after we use below code to catch the update command, we cannot get these original values, we could only touch these updated values:

    var updateCommand = interceptionContext.Result as DbUpdateCommandTree;
                    if (updateCommand != null)

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, August 27, 2014 10:05 AM