Use the SAVE and query pipelines to "Archive" deleted records (logical deletion)

This function is quite useful, and there are many practical scenarios. It is also relatively simple to operate in LS, so I would like to record it here.

 

[Original post address] using the SAVE and query pipeline to "Archive" deleted records

[Original article posting time] Fri, Nov 18 2011 pm

Before entering Microsoft, I used to work in the health care industry. My main job was to develop software for hospitals and medical insurance companies. In all these systems, we ensure patient data security through detailed review tracking (Change logging), authorization systems and complex business rules. A frequently-mentioned requirement is that we never delete patient information out of the system, but it only archives or "marks" as deleted. In this way, we can easily maintain historical data, but this data set is limited to the current patient.

Fortunately, lightswitch makes this simple, because before saving data, it allows us to enter the storage pipeline to perform data processing. We can also go to the query pipeline to filter data before it returns a value. In this blog, I will show you how to mark deletion records without deleting records in the database, and how to filter these records to make them invisible to users.

Enter the Save Pipeline

The storage pipeline runs in the middle layer (that is, the logic layer), and objects are updated, inserted, or deleted at any time. You can write business logic in it, process changes in the middle layer, and save the changes to the data storage area. (For more information about saving the MPs queue, see obtain the maximum output from the MPs queue in Visual Studio lightswitch..)

Suppose we have an application for working with customers. But we do not want to physically delete our customer records in the database. There are many ways to implement it. One way is to move the record to another table. This is similar to the audit trail example I have shown here. Another way is to use another field to mark the record as deleted. For example, let's look at a simple data model of a customer table and their orders. Please note that I have created a required field named "isdeleted" in the customer table, which is of the boolean type. I have deselected "Default display" in the Properties window, so this field is invisible on any interface.

To flag the isdeleted field programmatically, when you try to delete a customer record, you only need to select the customer entity in the Data designer, and then select the "write code" button from the drop-down menu, and selectCustomers_deletingMethod.

Below are two lines of code we need to write:

   1: Private Sub Customers_Deleting(entity As Customer) 

   2:     'First discard the changes, in this case this reverts the deletion 

   3:     entity.Details.DiscardChanges() 

   4:     'Next, change the IsDeleted flag to "True" 

   5:     entity.IsDeleted = True

   6: `End Sub

Please note that, first, we must call discardchanges to convert the entity back to its unchanged state. Then we simply set the isdeleted field to true. That's it! Remember, when we change the object state like this, the corresponding storage pipeline method will still run. For example, in this case, customers_updating will be triggered now, because we have changed the state of the object from deleted to unchanged and to modified. You can view the object status by using the entity. Details. entitystate attribute.

Enter query Pipeline

Now that we have successfully marked the deleted customers, we will filter them out in the query so that they are not displayed to the user on any interface. To apply a global filter in the system or Sort any customer entity, you can use one technique. Instead of creating a custom global query, you have to remember to use the query on all your interfaces. Instead, you only need to modify the built-in query generated by lightswitch. Lightswitch generates xxx_all, xxx_single, and xxx_singleordefault queries in all entities. You can modify them in the code. You can access them in the same way as accessing the MPs queue.

In the customer table, select the "write code" button from the drop-down menu of the Data designer, pull the scroll bar to query methods, and then selectCustomers_all_preprocessquery .

This query is the basis for all queries created for the user table. I usually use this method to sort records in a meaningful manner. In this way, the default queries of each object are sorted on each interface in the system. In this case, we need to filter out any customers whose isdeleted is marked as true, meaning that only records whose isdeleted is set to false are returned:

   1: Private Sub Customers_All_PreprocessQuery(ByRef query As System.Linq.IQueryable(Of Customer)) 

   2:     query = From c In query 

   3:     Where c.IsDeleted = False 

   4:     Order By c.LastName, c.FirstName

   5: End Sub

Note that we also have a table associated with the customer table named order. I can also make a similar filter in the order entity. This depends on how users navigate to orders, which may not be necessary. For example, if you only display orders on the customer details page, you do not have to worry about additional filters.

   1: Private Sub Orders_All_PreprocessQuery(ByRef query As System.Linq.IQueryable(Of Order))

   2:     query = From o In query 

   3:         Where o.Customer.IsDeleted = False 

   4:         Order By o.OrderDate Descending

   5: End Sub

Take actual actions!

Okay, it's time to test it and see if it works properly. I created a list and details page in the customer table. When we run it, I can perform normal operations such as insert, update, and delete. There is no need to change the interface, it is the same as the normal operation.

You marked it as deletion... .

.. Then click Save to execute our customers_deleting logic. Once a record is deleted and saved, it disappears from my interface list, but it still exists in the database. We can see that if we look at the customer table in the actual database.

Using the storage and query pipelines can provide you with tremendous data strength. This is only one of multiple methods. You can use them to operate data and write business rules.

Hope you like it!