19541

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

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; } } } }

Answer1:

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)));

Answer2:

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.

Answer3:

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=

Recommend

  • Perform Command For List of commands without using IF Statement C#
  • How to get a list of EntityObject's from an EF model
  • SQLDataAdapter.Update() not Updating when RowState = Modified
  • Laravel - many-to-many where the many-to-many table is (part-) polymorph
  • How to select a specific field additionally to a tables default fields?
  • how to pass url variable to next pages in php pagination
  • Change attribute of custom directive
  • Change device language on Android 6.0 (Android M)
  • div fade-in when window is scrolled a certain distance from the top
  • How to define an array of floats in Shader properties?
  • change color of jstree node
  • EF 4.1 DBContext AutoDetectChangesEnabled
  • How to move an element within a structure, possibly with zippers?
  • How can I reset dropdown data if modal closed on vue component?
  • Why can't UI components be accessed from a backgroundworker?
  • Create function that can pass a parameter without making a new component
  • How to pass solution folder as parameter in command line arguments (for debug)?
  • Outlines on links in IE9 remains when focus is changed
  • How to pass nginx proxy url for socket
  • Retrieving specified columns from a list of csv files to create a data data frame in R
  • Jquery Knockout: ko.computed() vs classic function?
  • In-place sed command not working
  • Creating a DropDownList
  • How to set elevation color?
  • How to getText() from the input field of an angularjs Application
  • Overlapping controls in Windows XP
  • MySQL Order by column = x, column asc?
  • Ensure fsync did its job
  • How to run “Deployd” on port 80 instead of port 5000 in webserver.
  • Magento Fatal error: Maximum execution error solution, on WAMP
  • How to set ini file attributes during an Inno install
  • Yii2: Config params vs. const/define
  • Python CGI os.system causing malformed header
  • NetLogo BehaviorSpace - Measure runs using reporters
  • recyclerView does not call the onBindViewHolder when scroll in the view
  • WinForms: two way TextBox problem
  • Calling of Constructors in a Java
  • Traverse Array and Display in markup
  • Transpose CSV data with awk (pivot transformation)
  • Why can't I rebase on to an ancestor of source changesets if on a different branch?