LinqToSQL: how to replace DELETE with UPDATE

Today I wrote a small web application which uses LinqToSQL and LinqToSQLDataSource to connect to the data source. This time, however, I thought that I could use a common pattern for deleting: instead of physically deleting records from the database I was going to update them (and set object.Deleted = true).

My first successive approach was to plug into LinqtoSQLDataSource processing pipeline:

   1: protected void TheLinqDataSource_Deleting( object sender, LinqDataSourceDeleteEventArgs e )

   2:  {

   3:      /* an item to delete is passed in arguments */

   4:      ACTIVITY Item = e.OriginalObject as ACTIVITY;

   5:      

   6:      /* create a new context */

   7:      ActivityMonitorClassesDataContext context = 

   8:         new ActivityMonitorClassesDataContext();

   9:      

  10:      /* retrieve the item once again in the new context */

  11:      ACTIVITY ItemToModify = 

  12:         ( from i in context.ACTIVITies where i.ID == Item.ID select i ).Single();

  13:  

  14:      /* update it */

  15:      ItemToModify.Deleted = true;

  16:      context.SubmitChanges();

  17:  

  18:      /* cancel the Deleting event from the processing pipeline */

  19:      e.Cancel = true;

  20:  }

This works like a charm but then I thought Wait! Why do I hack the view's processing pipeline? Couldn't I just pretend that the object is deleted and rather modify the way LinqToSQL actually deletes data?

So I removed the TheLinqDataSource_Deleting event and started to search for a place where I could plug into Linq's processing pipeline.

It seems that there are few partial methods generated in the LINQ model:

   1: [System.Data.Linq.Mapping.DatabaseAttribute(Name="ActivityMonitor")]

   2: public partial class ActivityMonitorClassesDataContext : 

   3:     System.Data.Linq.DataContext

   4: {

   5:     

   6:     private static System.Data.Linq.Mapping.MappingSource 

   7:         mappingSource = new AttributeMappingSource();

   8:     

   9: #region Extensibility Method Definitions

  10: partial void OnCreated();

  11: partial void InsertACTIVITY(ACTIVITY instance);

  12: partial void UpdateACTIVITY(ACTIVITY instance);

  13: partial void DeleteACTIVITY(ACTIVITY instance);

  14: #endregion

  15:  

  16: ...

If you provide an explicit implementation of these methods (Ben Hall explains on how Linq knows if these methods are implemented), you can alter the way Linq inserts, updates and deletes items.

At first I thought that following will work:

   1: public partial class ActivityMonitorClassesDataContext

   2: {

   3:     partial void DeleteACTIVITY( ACTIVITY instance )

   4:     {

   5:         instance.Deleted = true;

   6:  

   7:         /* redirect delete to update */

   8:         this.ExecuteDynamicUpdate( instance );

   9:     }

  10: }

however, it does not work. Linq knows that the entity should be deleted so the query generated for the update clause does not contain any new values and you end up with SqlException saying that there is an error near WHERE (the update query looks like this: "UPDATE ... SET WHERE ..." with just a blank space between SET and WHERE).

I would still love to see the code above working, in the meantime however, following code works:

   1: public partial class ActivityMonitorClassesDataContext

   2: {

   3:     partial void DeleteACTIVITY( ACTIVITY instance )

   4:     {

   5:         /* duplicate the context with its transaction */

   6:         ActivityMonitorClassesDataContext context = 

   7:             new ActivityMonitorClassesDataContext( this.Connection );

   8:         context.Transaction = this.Transaction;

   9:  

  10:         /* attach and modify the instance */

  11:         context.ACTIVITies.Attach( instance );

  12:  

  13:         instance.Deleted = true;

  14:  

  15:         context.SubmitChanges();

  16:     }

  17: }

I would love to learn a cleaner way to make it work.