The easiest way to create modern business applications for the Cloud and Office 365
This article presents a scenario at a sales team that shows the benefit of using composed and scalar queries. It presents a detailed walkthrough for creating composed and scalar queries in LightSwitch, and creating screens to display the query results.
Composed query is a query that operates on the results of another query. Scalar query is a query that returns one and only one row.
Consider a business application designed for a sales department at a medium sized organization. This application should equip the sales teams to slice and dice orders based on various parameters. Parameters such as customers in a certain geographic area, orders from a particular date range, products that sell at a high price and large orders. The sales team would also want to know the customer who spent the most on a single order.
Let us create a LightSwitch application that can help the sales team. To follow along with this example you will need Northwind database in addition to LightSwitch.
Create a project, attach to Northwind database and import Order Details, Orders, Products and Customers tables. We want to create a filter that can display all the orders from customers who live in a certain country. To do this, add a query under Order_Details table. Name the query “OrderDetailsForCustomersInCountry”.
On the query designer surface, click “+ Add Filter”. Select “Order.Customer.Country” for the filter’s left-operand, operator as “=” (is equal to) and then choose “@ Parameter” for the value type. For the filter’s right-operand value, click “Add New”. This will create a parameter by name "Country”. Make this parameter optional. To do this, open the properties window of the parameter and check “Is Optional”. The query designer should now resemble Figure 1.
Figure 1: Query Designer with “OrderDetailsForCustomersInCountry” Query
The parameter “Country” is marked optional since the value is set to a default value based on certain logic. For example, a sales team member in the United States would be more interested in orders from customers living in the United States. If the parameter is not optional then the team member will have to enter the value of the parameter each time the filter executes. PreprocessQuery() method makes it possible to write code that will set the default value of “Country”.
Click “Edit Additional Query Code” link in the properties window and add the following lines of code in the PreprocessQuery() method. This code first checks to see if the parameter is passed and if not, filters the query on customers in the USA:
Private Sub OrderDetailsForCustomersInCountry_PreprocessQuery
(Country As String, ByRef query As
If String.IsNullOrEmpty(Country) Then
query = From detail In query
Where detail.Order.Customer.Country = "USA"
partial void OrderDetailsForCustomersInCountry_PreprocessQuery
ref IQueryable<LightSwitchApplication.Order_Detail> query)
query = from details in query
where details.Order.Customer.Country == "USA"
Let us build a screen to display results of “OrderDetailsForCustomersInCountry”. Open “OrderDetailsForCustomersInCountry” in the Query designer. Click “Add Screen” on the designer’s command bar. Choose “Search Data Screen” template. Enter “CustomersByCountry” as the screen name. Choose “OrderDetailsForCustomersInCountry” as the screen data. See Figure 2.
Figure 2: Add New Screen Dialog for CustomerByCountry Search Screen
In the Screen designer, select “Order_DetailCountry” and open its properties. Enter “Filter by Country:” for the display name. Check “Is Parameter”. Uncheck “Is Required”. See Figure3.
Figure 3: Parameterized Search Screen
Run (F5) the application. The runtime should look as shown in Figure 4. Notice that “Filter by Country:” is blank and the number of pages returned is 8. The logic in PreprocessQuery() method filtered and returned only orders from customers in USA. Without this filtering, the number of pages returned will be greater than 8.
Figure 4: Orders Details from Customers in USA
Suppose the sales team members want to look at orders from customers in some other country, say Austria. In that case, they enter “Austria” in “Filter by Country:” field and hit Enter or Tab key. The filtered results will look as shown in Figure 5. Notice that the number of pages returned is 3.
Figure 5: Order Details from Customers in Austria
There are some benefits in using a query as a source of another query. The composed query reuses the logic built on the designer and the code written in the PreprocessQuery() method of the source query. In addition, it helps in maintainability. It is easier to update a single query than make changes on multiple queries.
The data source for “OrderDetailsForCustomersInCountry” is Order_Details. In LightSwitch, it is possible to choose a query as a source for another query. The composed query operates on the results obtained from the source query.
Let us build a composed query that uses the results from “OrderDetailsForCustomersInCountry”. Start by adding a new query under Order_Details. Name the query “OrderDetailsForDateRange”. From the Query designer’s command bar, change the Source of the query from Order_Details to “OrderDetailsForCustomersInCountry”. See Figure 6.
Figure 6: Choose the Source for “OrderDetailsForDateRange” Query
Notice that parameters from the source query, in this case Country, are available in the composed query. These inherited parameters will not be editable on the designer surface. However, the parameters will be available for the PreprocessQuery() method and on the designer surface, specifically as a filter’s right-operand.
“OrderDetailsForDateRange” filters orders with order date between a certain date ranges. The start and end dates are to be provided by the user. On the query designer surface, click “+ Add Filter”. Select “Order.OrderDate” for the left-operand of the filter. For the operator, choose “is between”. There will be two right-operands for “is between” operator. Add a “StartDate” parameter as the first right-operand and “EndDate” parameter as the second. Query designer should now look as shown in Figure 7.
Figure 7: Query Designer with “OrderDetailsForDateRange” Query
This section shows how a query can be a source for multiple queries. It also shows that a composed query can be a source for another query or queries.
The sales team needs to track products that sell at a high price. Each member of the sales team is interested in looking at only the products he/she is responsible to track. A high price product is one that has a unit price greater than $25. In addition, the product should be available in the market i.e., “Discontinued” field is set to false.
Add a new query under Order_Details. Name this query “OrderDetailsForHighPriceProducts”. Change the source of the query to “OrderDetailsForCustomersInCountry”. On the query designer surface, add two filters as shown in Figure 8.
Figure 8: Query Designer with “OrderDetailsForHighPriceProducts” Query
Now let us create another level of composition on “OrderDetailsForHighPriceProducts”. Among these high priced products, we want to filter out large purchases. Sales team defines a purchase of $2000 and greater as a large purchase.
Add a new query under Order_Details. Name this query “OrderDetailsForLargePurchase”. Change the source of the query to “OrderDetailsForHighPriceProducts”. The designer surface does not provide support to add logic that determines the price because it is a calculation of multiple fields. Hence, add the following code in the PreprocessQuery() method.
Private Sub OrderDetailsForLargePurchase_PreprocessQuery
(ByRef query As System.Linq.IQueryable
query = From detail In query
Where (detail.UnitPrice * detail.Quantity) > 2000
partial void OrderDetailsForLargePurchase_PreprocessQuery(ref IQueryable<Order_Detail> query)
query = from details in query
where (details.UnitPrice * details.Quantity) > 2000
“OrderDetailsForLargePurchase” is composed on “OrderDetailsForHighPriceProducts”, and “OrderDetailsForHighPriceProducts” is composed on “OrderDetailsForCustomersInCountry”. This demonstrates multi-level composition of queries.
“OrderDetailsForCustomersInCountry” is the source for “OrderDetailsForHighPriceProducts” and “OrderDetailsForDateRange”. This demonstrates that multiple queries can use a query as the data source.
You should notice that the source dropdown on the Query designer command bar lists only valid data sources. A valid data source is the parent entity or any other query under the same entity that will not cause a cyclical reference. A cyclical reference occurs when queries become data source for each other. If Query A is the source of Query B then Query B cannot be the source of Query A. In the sales team queries, “OrderDetailsForLargePurchase” cannot be the source of OrderDetailsForHighPriceProducts. However, both the queries can use Order_Details, “OrderDetailsForCustomersInCountry” or “OrderDetailsForDateRange” as their source.
The sales team wants to know their most valuable customer, the customer who spent the most on an order. To locate this one and only one customer we can create a query.
Add a query under Order_Details. Name it “BiggestPocketQuery”. Open the query’s Properties window. Change “Number of Results Returned:” property from Many to One. Observe that “Add Sort” button becomes disabled. Since the number of results returned for this query is one and sort on that single result is not applicable. This query should look as shown in Figure 9.
Figure 9: Set “BiggestPocketQuery” Query as Scalar
Please note that a scalar query cannot be a source for another query or a screen.
Add the following code in PreprocessQuery() method to compute the purchase price for each order and return the largest.
Private Sub BiggestPocketQuery_PreprocessQuery
(ByRef query As System.Linq.IQueryable
query = From detail In query
Order By (detail.UnitPrice * detail.Quantity) Descending
partial void BiggestPocketQuery_PreprocessQuery(ref IQueryable<Order_Detail> query)
query = query = query.OrderByDescending(od => od.UnitPrice * od.Quantity).Take(1);
This section will show how to build a screen that can display the result from a scalar query.
Open one of the non-scalar queries in the designer. On the Query designer’s command bar, click “Add Screen”. On “Add New Screen” dialog, choose “Search Data Screen” template. Enter “MostValuableCustomer” in the Screen Name. Leave Screen Data as “(None)”. Click OK. See Figure 10.
Figure 10: Add New Screen Dialog for Most Valuable Customer Screen
In the Screen designer, click “Add Data Item…” button on the command bar. See Figure 11.
Figure 11: Add Data Item
On “Add Data Item” dialog, choose “NorthwindData.BiggestPocketQuery”. Click OK. See Figure 12.
Figure 12: Add the Scalar Query to the Screen
In the Screen designer, drag the query onto the screen layout section as shown in Figure 13.
Figure 13: Add the Scalar Query to the Screen Layout
Run the application (F5) and open “Most Valuable Customer” screen. This should look as the shown in Figure 14.
Figure 14: Most Valuable Customer Screen
The Query designer in LightSwitch makes it simple for the developer to create composed and scalar queries. The PreprocessQuery() method equips the developer to write additional logic to filter the results. The Screen designer allows you to create screens that can display the results from the queries. Have fun building your own queries in LightSwitch!
Ravi Eda, thanks a lot for this Tutorial, worked 100% as described, I learned a lot about the queries, Since I'm not a .Net programmer or DBA, the queries and Linq are hard for me... Really good stuff here!
Thanks Manuel. I am new to LINQ too. Here are some resources that I use to learn LINQ: msdn.microsoft.com/.../aa904594.aspx
@Manuel - there's also a good "How Do I" video series on the VB Developer center :-): msdn.microsoft.com/.../ff718211
And there's a good set of LINQ samples organized here:
Beth, thanks a lot I will definitely check your videos again, I did it for the Beta 1.
Ravi, thanks again I will will check out the Linq resources as well... one more question... do you will release a beta 3 or first version of Lighswitch, I'm having trouble with the screens... regards ;)
Beth, very complete Linq videos, is a most for me now to learn about LightSwitch and Linq... thanks again