none
New feature: soft delete RRS feed

  • General discussion

  • See this tutorial for more information: http://azure.microsoft.com/en-us/documentation/articles/mobile-services-using-soft-delete/

    We have just released a new feature in Mobile Services, for both the JavaScript and .NET backends. A soft delete marks an item as deleted but does not permanently remove it from the table. This feature is useful in offline support, since it helps the client SDK determine which records to remove from its local database.

    Background

    Tables created in the either the JavaScript or .NET backend can optionally have soft delete enabled. When using soft delete, a new column called __deleted with SQL type BIT [TODO add hyperlink] is added to the database. Then, a delete operation does not physically delete rows from the database, but rather sets the value __deleted column to TRUE.

    When querying records on a table with soft delete enabled, by default deleted rows are not returned in the query. In order to request these rows, you must pass a query parameter __includeDeleted=true in your REST call. In the .NET client SDK, you can also use the helper method IMobileServiceTable.IncludeDeleted().

    Soft delete has a few potential benefits:

    • When using the offline sync [add URL] feature, the client SDK automatically queries for deleted records and removes them from the local database. Without soft delete, you need to write additional code on the backend so that the client SDK knows which records to remove from the local store. Otherwise, the client local store and backend will be inconsistent with regard to these deleted records and the client method PurgeAsync() must be called to clear the local store.
    • Some applications have a business requirement to never physically delete data, or to delete data only after it has been audited. The soft delete feature can be useful in this scenario.
    • Soft delete can be used to implement an "undelete" feature, so that data deleted by accident can be recovered.

    However, soft deleted records take up space in the database, so you should consider creating a scheduled job to periodically hard delete the soft deleted records. For an example of this, see the sample scheduled job below. Your client code should also periodically call PurgeAsync() so that these hard deleted records do not remain in the device's local data store.

    Enabling Soft Delete (JavaScript backend)

    To enable soft delete on an existing table in the JavaScript backend:

    1. Click the Data tab in the Management Portal and select the table.
    2. Click Enable Soft Delete in the command bar. If the table already has soft delete enabled, this button will not appear.

    To disable soft delete, select the table and click the Columns tab. Select the column __deleted and click the Delete button in the command bar.

    To enable soft delete on a new table, select the option on table creation:

    Enabling soft delete (.NET backend)

    In the .NET backend, soft delete can be enabled by passing a parameter to the constructor of EntityDomainManager. For instance, the Initialize method of your controller class would look something like this:

    protected override void Initialize(HttpControllerContext controllerContext)
    {
        base.Initialize(controllerContext);
        MobileService1Context context = new MobileService1Context();
        DomainManager = new EntityDomainManager<TodoItem>(context, Request, Services, enableSoftDelete: true);
    }

    Using soft delete in table scripts (JavaScript backend)

    To detect an undelete request, use the property "undelete" in your update table script:

    function update(item, user, request) {
        if (request.undelete) { /* any undelete specific code */; }
    }

    To include deleted records in query result in a script, set the "includeDeleted" parameter to true:

    tables.getTable('softdelete_scenarios').read({
                includeDeleted: true,
                success: function (results) {
                    request.respond(200, results)
                }
            });

    To retrieve deleted records via an HTTP request, add the query parameter "__includedeleted=true":

    http://youservice.azure-mobile.net/tables/todoitem?__includedeleted=true&__systemproperties=deleted

    Sample scheduled job for purging soft deleted records

    JavaScript backend

    This is a sample scheduled job that deletes records that were updated prior to a particular date:

    function purgedeleted() {
         mssql.query('DELETE FROM softdelete WHERE __deleted=1', {
            success: function(results) {
                console.log(results);
            },
            error: function(err) {
                console.log("error is: " + err);
        }});
    }

    To learn more about scheduled jobs, see: Schedule recurring jobs in Mobile Services.

    .NET backend

    The following scheduled job purges soft deleted records that are more than a month old:

    public class SampleJob : ScheduledJob
    {
        private MobileService1Context context;
     
        protected override void Initialize(ScheduledJobDescriptor scheduledJobDescriptor, CancellationToken cancellationToken)
        {
            base.Initialize(scheduledJobDescriptor, cancellationToken);
            context = new MobileService1Context();
        }
     
        public override Task ExecuteAsync()
        {
            Services.Log.Info("Purging old records");
            var monthAgo = DateTimeOffset.UtcNow.AddDays(-30);
     
            var toDelete = context.TodoItems.Where(x => x.Deleted == true && x.UpdatedAt <= monthAgo).ToArray();
            context.TodoItems.RemoveRange(toDelete);
            context.SaveChanges();
     
            return Task.FromResult(true);
        }
    }

    
    

    To learn more about scheduled jobs, see: http://azure.microsoft.com/en-us/documentation/articles/mobile-services-dotnet-backend-schedule-recurring-tasks/




    Friday, September 12, 2014 10:43 PM
    Moderator

All replies

  • Hello Donna,

    I am unable to find the ”Enable Soft Delete" button for the Java Script Backend, nor am I seeing any options when creating a new mobile service.  I accept that I am likely missing something...

    Please advise.  

    Thank you, ...Rob

    Monday, September 15, 2014 12:07 AM
  • Hi Rob,

    You may have run into a bug in the portal which we've now fixed. Please restart your browser and try again.


    Monday, September 15, 2014 7:12 PM
    Moderator
  • Donna,

    I now see the "Enable Soft Delete" button. 

    Thank you very much for your help.  ...Rob

    Monday, September 15, 2014 8:21 PM
  • Hi Donna,

    Nice to hear but how to enable soft delete with a MappedEntityDomainManger?

        public class SimpleMappedEntityDomainManager<TData, TModel>
       : MappedEntityDomainManager<TData, TModel>
            where TData : class, ITableData
            where TModel : class
        {
            private readonly Func<TModel, string> _keyString;
            public SimpleMappedEntityDomainManager(DbContext context,
                HttpRequestMessage request, ApiServices services,
                Func<TModel, string> keyString)
                : base(context, request, services)
            {
                _keyString = keyString;
            }
            public override SingleResult<TData> Lookup(string id)
            {
                return LookupEntity(p => _keyString(p) == id);
            }
            public override Task<TData> UpdateAsync(string id, Delta<TData> patch)
            {
                return UpdateEntityAsync(patch, id);
            }
            public override Task<bool> DeleteAsync(string id)
            {
                return DeleteItemAsync(id);
            }
        }

    This is needed for AutoMapper use.

    Regards.

    Jeremy

    Tuesday, September 16, 2014 2:33 PM
  • Jeremy, soft delete isn't yet available (I posted my original post too soon!), so I'll let you know when you can actually try it out.
    Friday, September 19, 2014 6:01 PM
    Moderator
  • Hi Donna,

    Any updates or delay about this?

    Regards,

    Jeremy

    Wednesday, October 29, 2014 9:51 AM
  • Hi Jeremy,

    This should be available now, make sure you are running the most recent version (439).  Let us know if you run into any issues or have more questions.

    Phil

    Wednesday, October 29, 2014 4:43 PM
    Moderator
  • Hi Phillip,

    As I can't install the 439 update due to the missing method exception explained in this post, I have to wait for a solution about it before...

    I noticed I'm not alone having this 439 post installation problem so I hope someone will share some advises soon.

    Thanks

    Thursday, October 30, 2014 10:50 AM
  • Ok as it's available since the 402 version, I was able to modify my code like:

    public class SimpleMappedEntityDomainManager<TData, TModel>
       : MappedEntityDomainManager<TData, TModel>
            where TData : class, ITableData
            where TModel : class
        {
            private readonly Func<TModel, string> _keyString;
            public SimpleMappedEntityDomainManager(DbContext context,
                HttpRequestMessage request, ApiServices services, bool enableSoftDelete,
                Func<TModel, string> keyString)
                : base(context, request, services)
            {
                EnableSoftDelete = enableSoftDelete;
                _keyString = keyString;
            }
            public override SingleResult<TData> Lookup(string id)
            {
                return LookupEntity(p => _keyString(p) == id);
            }
            public override Task<TData> UpdateAsync(string id, Delta<TData> patch)
            {
                return UpdateEntityAsync(patch, id);
            }
            public override Task<bool> DeleteAsync(string id)
            {
                return DeleteItemAsync(id);
            }
        }

    Here I can use DTO mapping and soft delete functionality with this custom DomainManager.

    Thanks.


    • Edited by Jeremy BP Wednesday, November 12, 2014 8:52 AM
    Wednesday, November 12, 2014 8:51 AM
  • Now I'm at 439 and I modified my domain manager to support soft delete, I'm able to soft delete item for very simple scenario.

    But what about relational database, I mean Parent/Child Relationship items like in this tutorial.

    In this case, no problem to soft delete the child but no way to soft delete the parent as it throw a conflict error.

    It's normal as Set.FindAsync() in the DeleteItemAsync(key) method of the DomainManager get the item with all its child items so then the Set.SubmitChangesAsync() try to add childs into the database, resulting in a primarykey violation.

    For now my workaround is to customise the Delete method on each controller to play with the item the same way as in the Patch method (as explained in this tutorial).

    Is cascading soft delete or simply soft delete in a relational schema is something you plan to work on?

    EDIT: Some details on my workaround

    1. Disable soft delete
    2. Make sure Patch is structured to deal with N:1 items
    3. Modify Delete method on each controller to call the Patch one if soft delete is activated:
    public async Task DeleteActivity(string id)
    {
    	string isSoftDeleteEnabledParameter; 
    	if (!(Services.Settings.TryGetValue("IsSoftDeleteEnabled", out isSoftDeleteEnabledParameter)))
    		Services.Log.Error("Could not retrieve IsSoftDeleteEnabled parameter.");
    
    	bool isSoftDeleteEnabled;
    	if(!bool.TryParse(isSoftDeleteEnabledParameter, out isSoftDeleteEnabled))
    		Services.Log.Error("IsSoftDeleteEnabled parameter should equal true or false value.");
    
    	if (isSoftDeleteEnabled)
    	{
    		var delta = new Delta<ActivityDTO>(typeof (ActivityDTO));
    		delta.TrySetPropertyValue("Deleted", true);
    		await PatchActivity(id, delta);
    	}
    	else
    	{
    		await DeleteAsync(id);
    	}
    }

    IsSoftDeleteEnabled is a web.config param wich is configurable from Azure configuration tab.

    • Edited by Jeremy BP Thursday, December 4, 2014 4:07 PM
    Thursday, December 4, 2014 2:08 PM
  • We don't currently support cascading soft delete, so you will have to write your own code to handle it.

    You could either use PATCH to manually do the cascading operation, or look into SQL triggers to have the database do it for you.

    Tuesday, December 9, 2014 11:54 PM
    Moderator