Beginning LightSwitch in VS 2012 Part 4: Too much information! Sorting and Filtering Data with Queries
NOTE: This is the Visual Studio 2012 version of the popular Beginning LightSwitch article series. For other versions see:
- Visual Studio 2013 & HTML Client: 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 4 of the Beginning LightSwitch in Visual Studio 2012 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 Search Data Screen. This screen has a built-in search feature that allows users to type in search terms and return rows where any string field matches that term. In this post I want to show you how you can define your own queries using the Query Designer and how you can use them on screens.
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: Sort and Filter Data on a Screen in a LightSwitch Application?
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 Search Data Screen for our Contacts, but if you notice the Contacts are not sorted when the screen is initially displayed. The user must click on the desired grid column to apply a sort manually. Once the user changes the sort order LightSwitch will remember that on a per-user basis, but we should really be sorting the Contacts for them when the Search screen is displayed the first time.
To create a 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 LastName property from the drop down. Click “ +Add Sort” again and this time select the FirstName property. Leave the order Ascending for both.
Before we jump into Filter conditions and Parameters, let’s see how we can use this simple query on our Search Screen. It’s actually easier in this case to re-create the Search Screen we created in Part 3 because we didn’t make any modifications to the layout. So in the Solution Explorer select the SearchContacts screen and delete it. Then right-click on the Screens node and select “Add Screen…” to open the Add New Screen dialog.
Select the Search Data Screen template then for the Screen Data you will see the SortedCOntacts query. Choose that then click OK.
Hit F5 to build and run the application and notice this time the contacts are sorted in alphabetical order immediately.
Defining Filter Conditions and Parameters
Our Search Screen is in better shape now but what if we wanted to allow the user to find contacts who’s birth date falls within a specific range? Out of the box, LightSwitch will automatically search across all String properties on an entity but not Dates. Therefore, in order to allow the user the ability to search within a birth date range, we need to define our own query.
So 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 Search Screen. In the Solution Explorer select the SearchSortedContacts screen and delete it. Then right-click on the Screens node and select “Add Screen…” to open the Add New Screen dialog again. Select the Search Data Screen template and for the Screen Data select the ContactsByBirthDate query and click OK.
Hit F5 to build and run the application. Notice the contacts are still sorted in alphabetical order on our search screen but you see additional fields at the top of the screen that let us specify the birth date range. LightSwitch sees that our query specified parameters so when we used it as the basis of the screen, the correct controls were automatically generated for us! And 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.
As you can see using queries with parameters like this allows you to perform much more specialized searches than what is provided by default. When using queries as the basis of Screen Data, LightSwitch will automatically look at the query’s parameters and create the corresponding screen parameters and controls which saves you time.
For more information on creating custom search screens, see: Creating a Custom Search Screen in Visual Studio LightSwitch and How to Create a Screen with Multiple Search Parameters in LightSwitch
For a video demonstration, see: How Do I: Create Custom Search Screens in LightSwitch?
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, 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 SearchTerm 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 SearchTerm
And finally add the last filter condition:
Or the Phone property contains the parameter of SearchTerm. I’ll also add a Sort on the PhoneNumber Ascending. The Query Designer should now look like this:
Now it’s time to create a Search Screen for this query. Instead of deleting the other Search screen that filters by birth date range, I’m going to create another new search 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:
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 Search Screen right-click on the Screens node again and select “Add Screen…” to open the Add New Screen dialog. Select the Search Data Screen template and for the Screen Data select the ContactsByPhone query this time and click OK.
Now before we run this I want to make a small change to the screen. The default behavior of a search screen is to make the first column a link that opens the Details screen for that record. Since we had to base our query on the PhoneNumber entity, LightSwitch will make the Phone property a link and not the Contact. So in the Screen Designer we need to make a small change. Select the Phone in the content tree and in the properties window uncheck “Show as Link”. Then select the Contact and check “Show as Link”.
Also change Display Name in the properties window for the PhoneNumberSearchTerm textbox to “Phone Number or Name” to make it more clear to the user what they are filtering on. And while we’re at it, we should disable the default search box by selecting the ContactsByPhone query on the left-hand side and unchecking “Supports search” in the properties window. This isn’t necessary since we are providing more search functionality with our own query.
Okay hit F5 and let’s see what we get. Open the “Search Contact By Phone” Search screen from the navigation menu and now users can search for contacts by name or by phone number. When you click on the Contact link, the 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 and you will see how to write your first lines of LightSwitch code! Until next time!
Enjoy!
Read the next article --> Beginning LightSwitch in VS 2012 Part 5: May I? Controlling Access with User Permissions