Using the Save and Query Pipeline to Flag and Filter Data with LightSwitch in Visual Studio 11

Note: This information applies to LightSwitch in Visual Studio 11 (LightSwitch V2)

In business applications sometimes we need to flag records with additional attributes in response to business rules and then consistently filter those flagged records in some way or another throughout the application. For instance we may have critical or historical data that must always be stored in our databases and never deleted. I used to work in the health care industry and it was important to keep historical patient data. Therefore we would not allow a user to delete a patient’s hospital visit information from the system. Over time, however this causes our data sets to get very large and distracts users from finding the relevant information they need. Hence, we need a way to flag these records and then filter this data out of our result sets across the entire application.

Last year I wrote an article based on LightSwitch in Visual Studio 2010 (LightSwitch V1) showing how you can use the Save & Query pipeline to archive and filter records instead – effectively marking them deleted but not actually deleting them from the system: Using the Save and Query Pipeline to “Archive” Deleted Records. With LightSwitch in Visual Studio 11 (LightSwitch V2) the filtering mechanism has been improved using the new entity set filters.

In this post I’ll show you how to use the new entity set filter methods to apply global filtering of records. But first, let’s recap how we can flag these records using the save pipeline.

Tapping into the Save Pipeline

The save pipeline runs in the middle tier anytime an entity is being updated, inserted or deleted. This is where you can write business logic that runs as changes are processed on the middle tier and saved to data storage. Let’s take an example where we don’t want to ever physically delete customers from the system. Here’s our data model for this example. Notice that I’ve created a required field called “IsDeleted” on Customer that is the type Boolean. I’ve unchecked “Display by Default” in the properties window so that the field isn’t visible on any screens.

In order to mark the IsDeleted field programmatically when a user attempts to delete a customer, just select the Customer entity in the data designer and drop down the “Write Code” button and select Customers_Deleting method.

Here are the 2 lines of code we need to write:

 Private Sub Customers_Deleting(entity As Customer)
    'First discard the changes, in this case this reverts the deletion
    entity.Details.DiscardChanges()
    'Next, change the IsDeleted flag to "True"
    entity.IsDeleted = True
End Sub

Notice that first we must call DiscardChanges in order to revert the entity back to it’s unchanged state. Then we simply set the IsDeleted field to True which puts the entity into a change state. So the appropriate save pipeline methods (Customers_Updating & Customers_Updated) will now run automatically. You can check the state of an entity by using the entity.Details.EntityState property.

This save pipeline code remains unchanged from LightSwitch V1. However in LightSwitch V2, we need to filter the data differently. Let’s see how.

Using Entity Set Filters

Now that we’re successfully marking deleted customers, the next thing to do is filter them out of our queries so that they don’t display to the user on any of the screens they work with. In LightSwitch V2 a new query interception method, EntitySet_ Filter, has been added which allows you to specify a filter that is applied whenever an entity set is referenced. (See the recent article on the LightSwitch Team Blog by Michael Simons: Filtering Data using Entity Set Filters for details on this new feature.)

So instead of using the Customers_All_PreprocessQuery like we had to in LightSwitch V1 as demonstrated here, we can apply the IsDeleted filter in the Customers_Filter method. Using the entity set Filter method is preferred over the All_PreprocessQuery method because the Filter method will execute any time the entity set is referenced, even when the entity has relationships and is not the direct target of a query. This enables true row level filtering across the application.

So for this example, select the Customer entity in the data designer and drop down the “Write Code” button and select Customers_Filter method.

Here we need to write a filter predicate as a lambda expression.

 Private Sub Customers_Filter(ByRef filter As Expressions.Expression(Of Func(Of Customer, Boolean)))
    filter =  Function(c) c.IsDeleted =  False
End Sub

These expressions can seem a little tricky at first but they just take some practice. You can think if these as in-line functions. This one takes a customer parameter and then you return a Boolean expression used to filter the records. You can filter on anything you want, even filter based on security permissions like Michael showed in his blog post. (For more information on lambda expressions see: Lambda Expressions (Visual Basic) and Lambda Expressions (C#).)

Now that we’ve got our Save and Query pipelines set up to handle the “deleted” Customers, we can run the application. When a user deletes a Customer, the behavior is exactly the same as if the Customer was physically deleted from the database.

And if we peek inside the actual Customer table in the database you can see them flagged correctly.

Also, now when we write a query where Customers is not the direct target, the row filtering will still apply. For instance, we have a parent table called Regions. If we wanted to create a query “Regions Over 10 Customers” we would write our query like so:

 Private Sub RegionsOver10Customers_PreprocessQuery(ByRef query As IQueryable(Of Region))
    query = From r In query
            Where r.Customers.Count > 10
End Sub

In LightSwitch V1 the additional filter in the Customers_All_PreProcessQuery would not be called when we executed this query. It would include the count of the Customers flagged IsDeleted – which isn’t what we want. You would have to include that filter in this query as well. So if you had a lot of queries in your application that indirectly reference the Customer, then this could get difficult to maintain. In LightSwitch V2 the Customers_Filter method is always called anytime the Customer entity set is referenced, regardless if it is the direct target of a query or not, so this query would always return the expected results.

Because we want to encourage using this approach to achieve true row level filtering, for new LightSwitch V2 projects there is no entry point for EntitySet_All_PreProcessQuery methods anymore. With upgraded projects from V1 they will still run the same as before and you will see them as modeled queries in the Solution Explorer. So there are no breaking changes to your application, but you will want to strongly consider using the entity set Filter methods instead.

Wrap Up

I hope this post has demonstrated how powerful and simple LightSwitch can be when working with data in the save and query pipelines. We’ve beefed up the global filtering technique so that it can handle scenarios like I showed above as well as row level security scenarios Michael demonstrated. If you were using the _All_PreProcessQuery methods, then they will continue to run when you upgrade to Visual Studio 11. However you should be using the entity set Filter methods instead for applying global filters that will run no matter how the entities are accessed.

Enjoy!

Comments

• Anonymous
  April 26, 2012
  This is going to come in handy.

• Anonymous
  April 30, 2012
  Beth, in your previous tutorial you also used sorting: Private Sub Customers_All_PreprocessQuery(ByRef query As System.Linq.IQueryable(Of Customer))    query = From c In query            Where c.IsDeleted = False Order By c.LastName, c.FirstName End Sub I usually sorted using the PreprocessQuery too, but now that it is hidden, what is the best way to filter and then sort?

• Anonymous
  April 30, 2012
  The comment has been removed

• Anonymous
  September 02, 2012
  Hello Beth I am having troubles using filters based on an associated entity. Let's suppose that customers may have some regions associated, and I would like to show only the customers that have a region associated with more than 50.000 persons. How can we build the filter? Thanks

• Anonymous
  September 02, 2012
  I found it filter = e => e.regions.Any((detail) => detail.Name.Trim().Equals("RegionName"));