How can I suppress execution from an Entity Framework 6 IDbCommandTreeInterceptor?

Question

I implemented the soft-delete pattern demonstrated by Rowan Miller during a TechEd session but I ran into an immediate problem because I am using inheritance in my Code First model. The first error was during queries because I put the IsDeleted property on my supertype (base class) but when I intercepted the query for a subtype and tried to add the filter, EF complained that there was no such property on that type. Fair enough, I moved the properties to the subtypes and that bit worked okay. But when it came to deleting, the command tree interceptor changed the delete of the subtype to an 'update set isdeleted=1' but EF also generated a delete for the supertype (base class). This caused a foreign key constraint error in the database. This is a bit of a pain but I could fix it by suppressing execution of the delete command for the supertype.

However, I cannot find a SuppressExecution method on the interception context and if I set the result to null I get a nullref exception. I guess I need some way to replace the command with a NullDbCommand, or similar. Any ideas?

public class CommandTreeInterceptor : IDbCommandTreeInterceptor
{
    public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext)
    {
        if (interceptionContext.OriginalResult.DataSpace != DataSpace.SSpace) return;

        // Look for query and add 'IsDeleted = 0' filter.
        var queryCommand = interceptionContext.Result as DbQueryCommandTree;
        if (queryCommand != null)
        {
            var newQuery = queryCommand.Query.Accept(new SoftDeleteQueryVisitor());
            interceptionContext.Result = new DbQueryCommandTree(queryCommand.MetadataWorkspace,
                queryCommand.DataSpace, newQuery);
        }

        // Look for delete and change it to an update instead.
        var deleteCommand = interceptionContext.OriginalResult as DbDeleteCommandTree;
        if (deleteCommand != null)
        {
            // !!! Need to suppress this whole command for supertypes (base class).

            var column = SoftDeleteAttribute.GetSoftDeleteColumnName(deleteCommand.Target.Variable.ResultType.EdmType);
            if (column != null)
            {
                var setClause =
                    DbExpressionBuilder.SetClause(
                        deleteCommand.Target.VariableType.Variable(deleteCommand.Target.VariableName)
                            .Property(column), DbExpression.FromBoolean(true));

                var update = new DbUpdateCommandTree(deleteCommand.MetadataWorkspace,
                    deleteCommand.DataSpace,
                    deleteCommand.Target,
                    deleteCommand.Predicate,
                    new List<DbModificationClause>{ setClause }.AsReadOnly(), null);

                interceptionContext.Result = update;
            }
        }
    }
}

Decompress

Tumbleweeeeeed... Ever get the feeling you are on another planet? – Decompress

Answer 1

My solution to this is a bit hacky but it works. I first tried to create a NullDbCommandTree by inheriting from DbCommandTree; unfortunately most of the methods and constructors in the latter class are marked as internal, so no use.

Because I had to return some kind of command tree, I replaced the delete command tree with a DbFunctionCommandTree. I created a stored proc in the database that does nothing and it just gets called instead of the delete. It works okay for now.

The other modification I had to make to both the QueryVisitor and the command tree was to check whether the entity actually had the IsDeleted property, because in a class hierarchy only one has it. For the one that has it, we replace the delete with an update, and for the ones that don't we call the null function. So here is my command tree code now:

        // Look for for softdeletes delete.
        var deleteCommand = interceptionContext.OriginalResult as DbDeleteCommandTree;
        if (deleteCommand != null)
        {
            var columnName =
                SoftDeleteAttribute.GetSoftDeleteColumnName(deleteCommand.Target.Variable.ResultType.EdmType);
            if (columnName != null)
            {
                // If the IsDeleted property is on this class, then change the delete to an update,
                // otherwise suppress the whole delete command somehow?

                var tt = (EntityType) deleteCommand.Target.Variable.ResultType.EdmType;
                if (
                    tt.DeclaredMembers.Any(
                        m => m.Name.Equals(columnName, StringComparison.InvariantCultureIgnoreCase)))
                {
                    var setClause =
                        DbExpressionBuilder.SetClause(
                            deleteCommand.Target.VariableType.Variable(deleteCommand.Target.VariableName)
                                .Property(columnName), DbExpression.FromBoolean(true));

                    var update = new DbUpdateCommandTree(deleteCommand.MetadataWorkspace,
                        deleteCommand.DataSpace,
                        deleteCommand.Target,
                        deleteCommand.Predicate,
                        new List<DbModificationClause> {setClause}.AsReadOnly(), null);

                    interceptionContext.Result = update;
                }
                else
                {
                    interceptionContext.Result = CreateNullFunction(deleteCommand.MetadataWorkspace,
                        deleteCommand.DataSpace);
                }
            }
        }
    }

    private DbFunctionCommandTree CreateNullFunction(MetadataWorkspace metadataWorkspace, DataSpace dataSpace)
    {
        var function = EdmFunction.Create("usp_SoftDeleteNullFunction", "dbo", dataSpace,
            new EdmFunctionPayload { CommandText = "usp_SoftDeleteNullFunction" }, null);
        return new DbFunctionCommandTree(metadataWorkspace, dataSpace, function,
            TypeUsage.CreateStringTypeUsage(PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.String), false, true),
            null);
    }
}

and here is the query visitor code:

        var columnName = SoftDeleteAttribute.GetSoftDeleteColumnName((expression.Target.ElementType));

        if (columnName == null  || !expression.Target.ElementType.Members.Any(m => m.Name.Equals(columnName, StringComparison.InvariantCultureIgnoreCase)))
        {
            return base.Visit(expression);
        }

        var binding = expression.Bind();

        return binding.Filter(binding.VariableType.Variable(binding.VariableName).Property(columnName).NotEqual(DbExpression.FromBoolean(true)));

Decompress

PS I know this solution is a real hack so please chip in if you have a better idea how to do it. Personally, I would like EF to either support SuppressExecution or have a NullDbCommandTree, the former being the preferred solution. – Decompress

Actually, the preferred solution is soft-deletes in EF core. – Decompress

Couldn't find a solution either... But I've used new DbQueryCommandTree(deleteCommand.MetadataWorkspace, DataSpace.SSpace, DbExpressionBuilder.True); in my workaround. – Sedgewinn

That's interesting. We came to the conclusion that soft deletes are an anti-pattern, as described here: ayende.com/blog/4157/avoid-soft-deletes We got rid of them for everything but documents only, so that they could be restored. – Decompress

I don't think I would make a decision to abandon soft deletes based on a 8 year old article. – Careycarfare

@JohnTolar It was based on my own experience with two projects where I discovered soft-deletes to be an anti-pattern as described in that article. Age does not wither the truth. Regardless of the ORM, when you have property references where the property's entity throws a 'not found' exception but it exists in the database, your app gets peppered with defensive, fragile code. That is the anti-pattern. – Decompress

I wanted small-footprint solution like @Sedgewinn suggested. But unfortunately that one is provider-specific; for me (Devart/Postgres) it produces invalid SQL. So I went with the original idea but modified it to not require an actual, empty sproc. EdmFunction.Create("nullFunc", "db-default-schema", commandTree.DataSpace, new EdmFunctionPayload { CommandText = "select null" }, null) works for me (change the default schema for your purposes). – Heliometer

Answer 2

Have a read of soft delete pattern which sets the entity to detached for deleted items.

Here is the snippet of code from the above article:

public override int SaveChanges()
{
    foreach ( var entry in ChangeTracker.Entries()
          .Where( p => p.State == EntityState.Deleted ) )
    SoftDelete( entry );
    return base.SaveChanges();
}

private void SoftDelete( DbEntityEntry entry )
{
    Type entryEntityType  = entry.Entity.GetType();
    string tableName      = GetTableName( entryEntityType );
    string primaryKeyName = GetPrimaryKeyName( entryEntityType );
    string deletequery = string.Format(
        "UPDATE {0} SET IsDeleted = 1 WHERE {1} = @id", 
        tableName, primaryKeyName);

    Database.ExecuteSqlCommand(
        deletequery,
        new SqlParameter("@id", entry.OriginalValues[primaryKeyName] ) );


    // Marking it Unchanged prevents the hard delete
    //   entry.State = EntityState.Unchanged;

    // So does setting it to Detached:
    // And that is what EF does when it deletes an item

    // http://msdn.microsoft.com/en-us/data/jj592676.aspx
    entry.State = EntityState.Detached;
}

Also have a watch of video after 7 minutes: http://channel9.msdn.com/Events/TechEd/NorthAmerica/2014/DEV-B417#fbid=

Taam

Thanks. I looked at this approach and rejected it for good reasons which I cannot 100% recall. I think the reasons were to do with inheritance - the IsDeleted field may not be on the entity being deleted and there is no way to discover it. Also I need to intercept queries as well so need the Interceptor. And... something else which I cannot quite recall. But the above approach would work without inheritance for setting the IsDeleted flag only but not for queries. – Decompress

Answer 3

Create DbCommandInterceptor:

public class DataIntercepter : DbCommandInterceptor
{
    public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        base.ScalarExecuting(command, interceptionContext);
    }

    public override void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        base.ScalarExecuted(command, interceptionContext);
    }

    public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        base.NonQueryExecuting(command, interceptionContext);
    }

    public override void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        base.NonQueryExecuted(command, interceptionContext);
    }

    public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        base.ReaderExecuting(command, interceptionContext);
    }

    public override void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        base.ReaderExecuted(command, interceptionContext);
    }
}

Then add it anywhere in code before execution (global.asax should be fine):

DbInterception.Add(new DataIntercepter());

Then suppress the execution:

public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
    interceptionContext.SuppressExecution();
    base.NonQueryExecuting(command, interceptionContext);
}

OR, set your own result:

public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
    interceptionContext.Result = -1;
    base.NonQueryExecuting(command, interceptionContext);
}

Currently working on a SQL server loadbalancer plugin and saw this question. I just found the solution 5 min ago :) Hope it is of any help for you after 2 years.

Stoicism

Thanks for this. Not working on this code at the moment and don't want to go back to it right now ;) Your code only sees the SQL so can either suppress or hack the SQL code. My code inherits from IDbCommandTreeInterceptor and is called earlier in the execution to convert deletes to updates etc. Would be tricky to do that with SQL. – Decompress

No problem, I thought maybe this gives you more insight. Your question came up as the first result in my google search for the solution, this way I thought it might be helpfull for others. – Stoicism