Beginning LightSwitch in VS 2013 Part 4: Too much information! Sorting and Filtering Data with Queries

NOTE: This is the Visual Studio 2013 update of the popular Beginning LightSwitch article series. For previous versions see:

• Visual Studio 2012: Part 4: Too much information! Sorting and Filtering Data with Queries
• Visual Studio 2010: Part 4: Too much information! Sorting and Filtering Data with Queries

Welcome to Part 3 of the Beginning LightSwitch in Visual Studio 2013 series! In part 1, 2 and 3 we learned about entities, relationships and screens in Visual Studio LightSwitch. If you missed them:

• Part 1: What’s in a Table? Describing Your Data
• Part 2: Feel the Love - Defining Data Relationships
• Part 3: Screen Templates, Which One Do I Choose?

In this post I want to talk about queries . In real life a query is just a question. But when we talk about queries in the context of databases, we are referring to a query language used to request particular subsets of data from our database. You use queries to help users find the information they are looking for and focus them on the data needed for the task at hand. As your data grows, queries become extremely necessary to keep your application productive for users. Instead of searching an entire table one page at a time for the information you want, you use queries to narrow down the results to a manageable list. For example, if you want to know how many contacts live in California, you create a query that looks at the list of Contacts and checks the State in their Address.

If you’ve been following this article series, you actually already know how to execute queries in LightSwitch. In part 3 we built a specific search for our Browse Contacts Screen. This allows the user to search for contacts by first or last name. In this post I want to show you how you can define your own reusable queries using the Query Designer and how you can use them across your application.

The LightSwitch Query Designer

The Query Designer helps you construct queries sent to the backend data source in order to retrieve the entities you want. You use the designer to create filter conditions and specify sorting options. A query in LightSwitch is based on an entity in your data model (for example, a Contact entity). A query can also be based on other queries so they can be built-up easily. For instance, if you define a query called SortedContacts that sorts Contacts by their LastName property, you can then use this query as the source of other queries that return Contacts. This avoids having to repeat filter and/or sort conditions that you may want to apply on every query.

For a tour of the Query Designer, see Queries: Retrieving Information from a Data Source

For a video demonstration on how to use the Query Designer, see: How Do I: Filter Data on a Screen in the LightSwitch HTML Client?

Creating a “SortedContacts” Query

Let’s walk through some concrete examples of creating queries in LightSwitch using the Contact Manager Address Book application we’ve been building. In part 3 we built a screen query for our Contacts that sorts by FirstName and LastName. However, this query is specific to the Browse Contacts screen. What if we wanted to reuse the query in other parts of our application? Instead of modifying the query directly on the screen, you can use the Query Designer to define global queries related to your entities instead.

To create a global query, in the Solution Explorer right-click on the entity you want to base it on (in our case Contacts) and choose “Add Query”.

The Query Designer will open and the first thing you do is name your query. We’ll name ours “SortedContacts”. Once you do this, you will see the query listed under the entity in the Solution Explorer.

Next we need to define the sort order so click “ +Add Sort” in the Sort section of the designer then select the FirstName property from the drop down. Click “ +Add Sort” again and this time select the LastName property. Leave the order Ascending for both.

Now that we have this query that sorts our contacts, we can use it as basis of other contact queries so that if we ever want to change the sort order, all the queries based on this will update the sort. For instance, now we can create another query based on SortedContacts that applies a Filter condition.

But before we jump into Filter conditions and Parameters, notice how the SortedContacts now shows up as a choice for screen data when selecting a Browse Data Screen. Global queries show up this way.

Keep in mind you won’t see queries that return sets of data for screen templates that work with individual records like the View and Add/Edit Details screen templates.

Defining Filter Conditions and Parameters

What if we wanted to allow the user to find contacts who’s birth date falls within a specific range? Let’s create a query that filters by date range but this time we will specify the Source of the query be the SortedContacts query. Right-click on the Contacts entity and choose “Add Query” to open the Query Designer again. Name the query “ContactsByBirthDate” and then select “SortedContacts” in the Source drop down on the top right of the designer.

Now the query is sorted but we need to add a filter condition. Defining filter conditions can take some practice (like designing a good data model) but LightSwitch tries to make it as easy as possible while still remaining powerful. You can specify fairly complex conditions and groupings in your filter, however the one we need to define isn’t too complex. When you need to find records within a range of values you will need 2 conditions. Once that checks records fall “above” the minimum value and one that checks records fall “below” the maximum value.

So in the Query Designer, click “+ Add Filter” and specify the condition like so:

Where

the BirthDate property

is greater than or equal to

a parameter.

Then select “Add New” to add a new parameter.

The parameter’s name will default to “BirthDate” so change it to MinimumBirthDate down in the Parameters section.

Similarly, add the filter condition for “Where the BirthDate property is less than or equal to a new parameter called MaximumBirthDate”. The Query Designer should now look like this:

One last thing we want to think about with respect to parameters is whether they should be required or not. Meaning must the user fill out the filter criteria parameters in order to execute the query? In this case, I don’t want to force the user to enter either one so we want to make them optional. You do that by selecting the parameter and checking “Is Optional” in the properties window.

Okay now let’s use this query for our Browse Screen. Instead of creating a new screen and selecting this global query, we can change the current query we’re using on the Browse Contacts screen we created in Part 3. Open the screen, select the Contacts query on the left, then change the Query Source to Contacts By Birthdate. LightSwitch will only let us select from queries that return contacts, or the entity itself.

Once we do this you will see the parameters we need automatically added to the screen’s view model and bound to the new query’s parameters (indicated by a grey arrow). Delete the previous screen parameter (FindContact) from the view model, drag the new parameters onto the screen where you want them, and then change the controls to Date Picker controls. I also added the BirthDate field into the List and changed the List control to a Tile List.

Hit F5 to build and run the application. Notice the contacts are still sorted in alphabetical order on our browse screen but you see fields at the top of the screen that let us specify the birth date range. Since both of these parameters are optional, users can enter none, one, or both dates and the query will automatically execute correctly based on that criteria.

Quick tip for small form factors (or you have a lot of optional parameters). If most of your users will be using smaller devices like mobile phones, you probably want to conserve precious space on the screen. Or maybe you want a separate screen if you have a lot of optional parameters. Instead of putting the parameter fields above the list, we can put them in a popup instead. While the app is running in debug, flip to the screen designer. Add a Popup by clicking on the Popups node, name it “Filter” in the properties window and then drag the parameters into it.

Then add a button into the Command Bar that shows the Popup (this will be automatically selected, so just click OK on the Add Button dialog).

You can also set the icon of the newly added “Show Filter” button to a Filter icon using the properties window. When you’re done tweaking the screen designer, save all your files and then refresh your browser. You will now see a button in the command bar for filtering contacts. (Also notice that the Tile List will display as a normal List on smaller form factors.)

As you can see using queries with parameters like this allows you to perform specialized searches. When creating new screens with queries as the basis of screen data, LightSwitch will automatically look at the query’s parameters and create the corresponding screen parameters and controls. If you’re changing queries on existing screens, LightSwitch will create the corresponding screen parameters bound to the query parameters for you in your view model. Either way, you can display them exactly how you want to the user using the screen designer.

Querying Related Entities

Before we wrap this up I want to touch on one more type of query. What if we wanted to allow the user to search Contacts by phone number? If you recall our data is modeled so that Contacts can have many Phone Numbers so they are stored in a separate related table. In order to query these using the Query Designer, we need to base the query on the PhoneNumber entity, not Contact.

So right-click on the PhoneNumbers entity in the Solution Explorer and select “Add Query”. I’ll name it ContactsByPhone. Besides searching on the PhoneNumber I also want to allow users to search on the Contact’s LastName and FirstName. This is easy to do because the Query Designer will allow you to create conditions that filter on related parent tables, in this case the Contact. When you select the property, you can expand the Contact node and get at all the properties.

So in the Query Designer, click “+ Add Filter” and specify the condition like so:

Where the Contact’s LastName property

contains

a parameter

Then select “Add New” to add a new parameter.

The parameter’s name will default to “LastName” so change it to FindContact down in the Parameters section and make it optional by checking “Is Optional” in the properties window.

We’re going to use the same parameter for the rest of our conditions. This will allow the user to type their search criteria in one textbox and the query will search across all three fields for a match. So the next filter condition will be:

Or the Contact’s FirstName property contains the parameter of FindContact

And finally add the last filter condition:

Or the Phone property contains the parameter of FindContact. I’ll also add a Sort on the Contact.FirstName then by Contact.LastName then by Phone Ascending. The Query Designer should now look like this:

Now it’s time to create a Browse Screen for this query. Instead of deleting the other Browse screen that filters by birth date range, I’m going to create another new screen for this query. Another option would be to add the previous date range condition to this query, which would create a more complex query but would allow us to have one search screen that does it all. For this example let’s keep it simple, but here’s a hint on how you would construct the query by using a group:

Not only is complex grouping options supported, but you can also drop down the “Write Code” button at the top of the designer and write your own queries using LINQ. For more information on writing complex queries see: Queries: Retrieving Information from a Data Sourceand How to Create Composed and Scalar Queries

So to add the new Browse Screen right-click on the Screens node again and select “Add Screen…” to open the Add New Screen dialog. Select the Browse Data Screen template and for the Screen Data select the ContactsByPhone query and click OK.

Next make the screen look how we want. I’ll change the List control to a Tile List and remove all the fields except Contact, Phone and Phone Type. Change the Contact control to a Columns Layout and delete all the fields except FirstName & LastName. I’ll also make the Phone Type’s Font Style smaller in the properties window. Then change the “PhoneNumerFindContact” screen parameter to a TextBox, and set the Label Position to None and enter the Placeholder text "Find Contact”. The screen should look like this:

Next let’s hook up our tap behavior and Add button so we can add new contacts and view existing ones from this screen. Select the Command bar, Add a button and select the existing method showAddEditContact and set the Contact to (New Contact) . Click OK.

Change the button name to just “Add Contact” and set the Icon to “Add” using the properties window. Next select the Tile List and set the Tap action in the properties window. Select the existing method showViewContact and then set the Contact to ContactsByPhone.selectedItem.Contact. Click OK.

Finally, right-click on the Screens node in the Solution Explorer and select “Edit Screen Navigation”. Now that we have two Browse Screens, we can choose between them in our app by adding them to the global navigation. For more information on screen navigation see: New Navigation Features in LightSwitch HTML Client

You can also right-click on this screen in the Solution Explorer and set it as the Home screen so it will open first.

Okay hit F5 and let’s see what we get. Now users can search for contacts by name or by phone number. When you click on the Contact tile, the View Details screen we created in part 3 will open automatically.

Wrap Up

As you can see queries help narrow down the amount of data to just the information users need to get the task done. LightSwitch provides a simple, easy-to-use Query Designer that lets you base queries on entities as well as other queries. And the LightSwitch Screen Designer does all the heavy lifting for you when you base a screen on a query that uses parameters.

Writing good queries takes practice so I encourage you to work through the resources provided in the Working with Queries section on the LightSwitch Developer Center.

In the next post we’ll look at user permissions. Until next time!

Enjoy!

Read the next article –> Part 5: May I? Controlling Access with User Permissions