(For more resources on this topic, see here.)
Querying in LightSwitch
The following figure is based on the one you may review on the link mentioned earlier and schematically summarizes the architectural details:
Each entity set has a default All and Single as shown in the entity Category. All entity sets have a Save operation that saves the changes.
As defined, the entity sets are queryable and therefore query operations on these sets are allowed and supported. A query (query operation) requests an entity set(s) with optional filtering and sorting as shown, for example, in a simple, filtered, and sorted query on the Category entity.
Queries can be parameterized with one or more parameters returning single or multiple results (result sets). In addition to the defaults (for example, Category*(SELECT All) and Category), additional filtering and sorting predicates can be defined. Although queries are based on LINQ, all of the IQueryable LINQ operations are not supported.
The query passes through the following steps (the pipeline) before the results are returned.
- CanExecute—called to determine if this operation may be called or not
- Executing—called before the query is processed
- Pre-process query expression—builds up the final query expression
- Execution—LightSwitch passes the query expression to the data provider
- for execution
- Executed—after the query is processed but before returning the results
- ExecuteFailed—if the query operation failed
Querying a Single Entity
We will start off creating a Visual Studio LightSwitch project LSQueries6 using the Visual Basic Template as shown (the same can be carried out with a C# template). We will attach this application to the SQL Server Express server's Northwind database and bring in the Products (table) entity.
We will create a screen EditableProductList which brings up all the data in the Products entity as shown in the previous screenshot.
The above screen was created using the Editable Grid Screen template as shown next with the source of data being the Products entity.
We see that the EditableProductList screen is displaying all columns including those discontinued items and it is editable as seen by the controls on the displayed screen. This is equivalent to the SQL query, Select * from Products as far as display is concerned.
Filtering and sorting the data
Often you do not need all the columns but only a few columns of importance for your immediate needs, which besides being sufficient, enormously reduces the cost of running a query. What do you do to achieve this? Of course, you filter the data by posing a query to the entity.
Let us now say, we want products listing ProductID, ProductName excluding the discontinued items. We also need the list sorted. In SQL Syntax, this reduces to:
SELECT [Product List].ProductID, [Product List].ProductName
FROM Products AS [Product List]
WHERE ((([Product List].Discontinued) =0))
ORDER BY [Product List].ProductName;
This is a typical filtering of data followed by sorting the filtered data.
Filtering the data
In LightSwitch, this filtering is carried out as shown in the following steps:
- Click on Query menu item in the LSQueries Designer as shown:
The Designer (short for Query Designer) pops-up as shown and the following changes are made in the IDE: A default Query1 gets added to the Products entity on which it is based as shown; the Query1 property window is displayed and the Query Designer window is displayed. Query1 can be renamed in its Properties window (this will be renamed as Product List). The query target is the Products table and the return type is Product.
As you can see Microsoft has provided all the necessary basic querying in this designer. If the query has to be changed to something more complicated, the Edit Additional Query Code link can be clicked to access the ProductListDataService as shown:
Well, this is not a SQL Query but a LINQ Query working in the IDE. We know that entities are not just for relational data and this makes perfect sense because of the known advantages of LINQ for queries (review the following link: http://msdn.microsoft.com/en-us/library/bb425822.aspx). One of those main advantages is that you can write the query inVB or C#, and the DataContext, the main player takes it to SQL and runs queries that SQL Databases understand. It's more like a language translation for queries with many more advantages than the one mentioned.
- Hover over Add Filter to review what this will do as shown:
This control will add a new filter condition. Note that Query1 has been renamed (right-click on Query1 and choose Rename) to ProductList.
- Click on the Add Filter button.
The Filter area changes to display the following:
The first field in the entity will come up by default as shown for the filtered field for the 'Where' clause. The GUI is helping to build up "Where CategoryID = ". However, as you can see from the composite screenshot (four screens were integrated to create this screenshot) built from using all the drop-down options that you can indeed filter any of the columns and choose any of the built-in criteria. Depending on the choice, you can also add parameter(s) with this UI.
- For the particular SQL Query we started with, choose the drop-down as shown.
- Add a Search Data Screen using the previous query as the source by providing the following information to the screen designer (associating the ProductList query for the Screen Data).
Notice that LightSwitch was intelligent enough to get the right data type of value for the Boolean field Discontinued. You also have an icon (in red, left of Where) to click on should you desire to delete the query.
This screen when displayed shows all products not discontinued as shown. The Discontinued column has been dragged to the position shown in the displayed screen.
(For more resources on this topic, see here.)
Selecting the columns to display
It is seldom necessary to show all the columns in a row of data for the entity. Getting all the columns may also impact the resources. We may therefore need to select only a few columns to display. What we will be doing in this section could have been done earlier as well. We will use the Customization Screen to remove columns; we do not need to show. Note that the Query Designer is not built to select the columns and it will return the whole set of columns. This feature is quite different from all the rest of Microsoft's Query Designers over the years. You can select the columns you need to display either in the Customization mode while the program is running, or in the Screen Layout by deleting the elements you do not need.
- Click on Customization Screen at top-right and delete all the rows except ProductID, and Product so that the Customization Mode screen appears as shown.
- Click on Save.
The changes you made will take effect and the SearchProduct screen will appear as shown:
Sorting the list
It is always important to display to the user a sorted list, either alphabetically or in some other way depending on the data type we need to display. Sorting makes it easy to get to the item you want quickly.
The list you see does not seem to be sorted whereas the SQL Query has the Order By Clause and orders' Product Name according to the default ordering.
Well, LightSwitch can be used to sort the query we created earlier. We will see how in the next section:
- Create a query exactly as before called SortedProductList.
- Click on the + sign by the side of Sort in the designer, which adds an expression developer GUI as shown:
- Click on the drop-down handle for the fields and pick ProductName from the list.
- Build the project.
- Add a Search Data Screen called Search Sorted Product List and remove all the unwanted columns as before.
- Click on F5 to display the screen.
- Verify that the list is sorted with the Product Name alphabetically as shown.
Also note that you can sort on multiple columns as well as add more complex criteria for selection as shown next:
Note however, the sorting on two columns (the above image) does not result in the desired result due to a known bug in Beta 2. This is fixed in the RTM according to what is reported in the LightSwitch forums; (http://social.msdn.microsoft.com/Forums/is/lightswitchgeneral/thread/47c28477-403a-45cd-aa40-67d36eb1b6d7).
Queries using a parameter
At times even the previous filtering is not good enough and you want to fine tune your query (search) even more. Let us say in the previous filtered data we know that the products' unit price ranges over a wide range. We may want to look at only those products which are above a certain value. How is this done?
Users familiar with Microsoft Access know how to find a solution to the above problem, that is, by using a parametric query. The next snippet which is a SQLView of a parametric query shows that you create a placeholder in the SQL Statement, for say, the ProductName. At runtime, Access will ask you for a ProductName and if you provide one it will show all the columns for that product.
In SQL Server, you could write a stored procedure; (http://msdn.microsoft.com/en-us/library/aa174792%28v=sql.80%29.aspx) such as the one shown next which when executed with a parameter can show this kind of information. If you have never worked with stored procedures you can find a simple explanation at: http://hodentekhelp.blogspot.com/2011/06/what-is-stored-procedure.html. The following code when executed in the query window of SQL Server Express will create the stored procedure. In this case, you first declare a parameter (@uprice in this example) and in order to execute the stored procedure you need to supply a value for that parameter.
Create proc PriceFix @uprice money
select ProductId, Productname
where Discontinued=0 and UnitPrice >@uprice
order by ProductName asc
This stored procedure is executed with the following statement, for example to find the selected items which are not discontinued and whose unit price is more than $45:
Exec PriceFix 45.0—Here you are providing a value for @uprice=45
The result of running query returns this the following set. It returns multiple values because there are many products that comply with the statement in the stored procedure.
18 Carnarvon Tigers
38 Côte de Blaye
43 Ipoh Coffee
51 Manjimup Dried Apples
59 Raclette Courdavault
20 Sir Rodney's Marmalade
62 Tarte au sucre
This kind of querying is carried out by creating a parameterized query in LightSwitch. The final result is as shown in the next screenshot. This is the same result as shown above.
To achieve this kind of screen to display those products whose price is greater than $45.00 the following abbreviated steps must be followed.
- Create a query ListByPrice as shown in the next image.
This is a development of the sorted list with the additional information about the parameter Uprice. Uprice is the parameter of type Money and you are using it in the Filter criterion in addition to the Discontinued column set to False. The filter regions and the corresponding SQL they follow are shown alongside. It should not be construed that the stored procedure is being used in the screen. It is just a conceptual mapping.
In the above case, you can add a parameter first and then use it in the Filter criterion, or you start from Filter criterion, add a new parameter and set its value from the default Decimal to Money.
- As shown in this first step, add a new parameter.
- Create a Select Data Screen with no screen data (here it is called SearchProductListByPrice).
- Click on Add Data Item to bring up the Add Data Item window as shown:
- In the Add Data Item window choose Queries and in the list choose ProductListData.ListbyPrice. The default name of the screen item becomes ListbyPrice.
- Click on OK on the Add Data Item window.
The screen appears as shown next:
- Click and drag ListbyPrice on the left-hand side of the screen and drop it just below the Rows Layout at the location shown.
The screen changes to the following as shown. The parameter is correctly identified as of data type Money and gets added to the screen menu.
The control for Unit Price is the default Money Editor which can be changed to a Text Box or Money Viewer.
- Keep the default control.
- Click on F5.
- The screen gets displayed with the Unit Price textbox with a value of 0 and you can enter any value you like. Here, a value of 45 was entered and the grid filled up with the rows that satisfy the criterion.
- The screen is now displayed for the selected parameter value as shown next:
In this article we described querying data for displaying data in Microsoft LightSwitch from single entities. Filtering, sorting, and the use of parameters (one or more) were described as well.
- Load Testing Using Visual Studio 2008: Part 1 [Article]
- Microsoft LightSwitch: Querying Multiple Entities [Article]
- Creating a Simple Report with Visual Studio 2008 [Article]
- Windows Development Using Visual Studio 2008 [Article]
- Microsoft LightSwitch Application using SQL Azure Database [Article]
- Connecting to Microsoft SQL Server Compact 3.5 with Visual Studio [Article]