Windows Phone 7 Database Rapid Repository V2.0 Querying with Views and Filters

Rapid Repository is a Windows Phone 7 Database and Silverlight Isolated Storage Database.

Download Rapid Repository from Microsoft Codeplex

Blog Tutorials

Views and Filters are the main features driving Rapid Repository V2.0.

The purpose of Views and Filters are to greatly improve performance when querying the database.

The following examples will show how to create and use views and filters.

For the examples we are going to be using the following entities:

Customer
  1. public class Customer : IRapidEntity
  2. {
  3.     public Guid Id { get; set; }
  4.     public string Title {get;set;}
  5.     public string FirstName { get; set; }
  6.     public string LastName { get; set; }
  7.     public string PhoneNumber { get; set; }
  8.     public string MobileNumber { get; set; }
  9.     public Address Address { get; set; }
  10. }
  11.  
  12. public class Address
  13. {
  14.     public string HouseNumber { get; set; }
  15.     public string PostCode { get; set; }
  16. }

 

What are Views and why use them?

Views are simply a subset of an entity, lets say you are designing a page that lists the last name and postal code of each customer, it would be really inefficient to load all of the entity data just to display this limited amount of data (especially if you have a lot of data).

In this scenario, the page would be better served with a flattened list of classes containing just the information it requires for the page, this is called a View Model.

For the rest of the examples we are going to create and use the following View Model:

Customer View Model
  1. public class CustomerViewModel : IRapidView
  2. {
  3.     public Guid Id { get; set; }
  4.     public string LastName { get; set; }
  5.     public string PostCode { get; set; }
  6. }

You will notice that along with the data required for displaying on the page, there is also an Id property enforced by the IRapidView interface.

This Id is set to the Id of the actual entity when the view is created, this allows GetById to be called using the View Model Id and have the actual entity returned.

 

Creating the View

Creating the view is really simple although it does require the use of lambda.

During application start up or in the MainPage constructor, you need to set up the mapping for the view.

This is achieved by mapping the View Model properties to it’s corresponding entity property.

The following example creates the mapping for the CustomerViewModel:

Setup View Mapping
  1. public MainPage()
  2. {
  3.     InitializeComponent();
  4.  
  5.     RapidRepository<Customer>.AddView<CustomerViewModel>(x => new CustomerViewModel { LastName = x.LastName, PostCode = x.Address.PostCode });
  6. }

As you can see, in this case a mapping has been created for the CustomerViewModel in the main page constructor.

 

Querying the View

Once the mapping is setup, you can query the view data by using the Query method on the repository.

The following example shows the View being queried and filtered to only retrieve customers from south west London.

Querying the View
  1. public void DisplayCustomersInSouthWestLondon()
  2. {
  3.     RapidRepository<Customer> repository = new RapidRepository<Customer>();
  4.  
  5.     IList<CustomerViewModel> londonCustomers = repository.Query<CustomerViewModel>()
  6.         .Where(x => x.PostCode.StartsWith("SW")).ToList();
  7.  
  8.     myListBox.ItemSource = londonCustomers;
  9. }

First thing to notice is the View type is passed as a generic argument, this is because an entity could have many different views for different pages.

As previously mentioned, you can get the actual entity from the database using the Id of a View.

Get Customer Entity
  1. public void DisplayCustomersInSouthWestLondon()
  2. {
  3.     RapidRepository<Customer> repository = new RapidRepository<Customer>();
  4.  
  5.     IList<CustomerViewModel> londonCustomers = repository.Query<CustomerViewModel>()
  6.         .Where(x => x.PostCode.StartsWith("SW")).ToList();
  7.  
  8.     Customer firstCustomerInList = repository.GetById(londonCustomers[0].Id);
  9. }

 

Filtering the View

In the example so far, we are retrieving the View and then filtering it further by postal code, if the page is actually only ever going to display customers from London we could pre filter the view.

This further aids performance and reduces the code we have to write when querying the database.

Pre-filtering is achieved using the AddFilter method when the View mapping is set up.

Adding a filter.
  1. public MainPage()
  2. {
  3.     InitializeComponent();
  4.  
  5.     RapidRepository<Customer>.AddView<CustomerViewModel>(x => new CustomerViewModel { LastName = x.LastName, PostCode = x.Address.PostCode })
  6.         .AddFilter(x => x.PostCode.StartsWith("SW"));
  7. }

As you can see, the same filter we were achieving using lambda is now a fixed filter for the view, this means that only the data that is actually required for this View is saved.

You can add multiple filters using the fluent syntax.

Multiple Filters
  1. public MainPage()
  2. {
  3.     InitializeComponent();
  4.  
  5.     RapidRepository<Customer>.AddView<CustomerViewModel>(x => new CustomerViewModel { LastName = x.LastName, PostCode = x.Address.PostCode })
  6.         .AddFilter(x => x.PostCode.StartsWith("SW"))
  7.         .AddFilter(x => x.LastName.Contains("Mc"));
  8. }

 

Synchronising The View (Used when upgrading)

If you are upgrading from V1.0 and already have saved entities that you will now be using a View Model for displaying data, you will need to synchronise the data in the database.

This can be done as a one-off during your upgrade script or even better, if you can afford the small overhead during application start-up do it then so it re-synchronises each time the user starts the app (it shouldn’t need to be done, this is only for the more pessimistic coder).

Synchronising the View
  1. public MainPage()
  2. {
  3.     InitializeComponent();
  4.  
  5.     RapidRepository<Customer>.AddView<CustomerViewModel>(x => new CustomerViewModel { LastName = x.LastName, PostCode = x.Address.PostCode })
  6.         .AddFilter(x => x.PostCode.StartsWith("SW"));
  7.  
  8.     RapidRepository<Customer>.SynchroniseView<CustomerViewModel>();
  9. }

Make sure you’ve registered the view mapping before calling synchronise.

 

Summary

Hopefully you agree that this is a really powerful addition to the repository and will greatly improve performance when querying the database.

I hope you find this useful.

Sean McAlinden.

No Comments