How to Create Composed and Scalar Queries (Ravi Eda)

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.

Business Case

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.

LightSwitch Application for the Sales Team

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:

Visual Basic:

        Private Sub OrderDetailsForCustomersInCountry_PreprocessQuery
                    (Country As String, ByRef query As 
                     System.Linq.IQueryable(Of LightSwitchApplication.Order_Detail))
            If String.IsNullOrEmpty(Country) Then
                query = From detail In query
                        Where detail.Order.Customer.Country = "USA"
            End If
        End Sub

C#:

partial void OrderDetailsForCustomersInCountry_PreprocessQuery
    (string Country, 
    ref IQueryable<LightSwitchApplication.Order_Detail> query)
{
    if (String.IsNullOrEmpty(Country))
    {
        query = from details in query
                where details.Order.Customer.Country == "USA"
                select details;
    }
}

Search Screens

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

Composed Queries

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

Hierarchy of Composed Queries

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

Multi-Level Composition

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.

Visual Basic:

        Private Sub OrderDetailsForLargePurchase_PreprocessQuery
                    (ByRef query As System.Linq.IQueryable
                        (Of LightSwitchApplication.Order_Detail))
            query = From detail In query
                    Where (detail.UnitPrice * detail.Quantity) > 2000
        End Sub

C#:

partial void OrderDetailsForLargePurchase_PreprocessQuery(ref IQueryable<Order_Detail> query)
{
    query = from details in query
            where (details.UnitPrice * details.Quantity) > 2000
            select details;
}

“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.

Scalar Queries

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.

Visual Basic:

        Private Sub BiggestPocketQuery_PreprocessQuery
                    (ByRef query As System.Linq.IQueryable
                    (Of LightSwitchApplication.Order_Detail))
            query = From detail In query
                    Order By (detail.UnitPrice * detail.Quantity) Descending
                    Take (1)
        End Sub

C#:

partial void BiggestPocketQuery_PreprocessQuery(ref IQueryable<Order_Detail> query)
{
    query = query = query.OrderByDescending(od => od.UnitPrice * od.Quantity).Take(1);
}

 

Screen for Scalar Query

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

Conclusion

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!