C H A P T E R 6
Querying Data
Along with creating tables and building screens, writing queries forms a key part of building a LightSwitch application. Queries enable you to filter data, and the results can be consumed by screens or other UI elements.
LightSwitch provides a designer that allows you to graphically build a query. For more-complex scenarios, you often need to extend queries by writing code. This chapter introduces you to the graphical designer, describes how you can extend queries by writing LINQ code, and shows you what happens inside the query pipeline.
Understanding Query Features
We’ll start this chapter by describing some of the characteristics and features of queries in LightSwitch. The easiest way for you to create a query is to use the query designer, but there are several other ways that queries can be defined. The four main ways are shown in Table 6-1.
Of course, every query needs a data source. The data sources that queries can use are as follows:
- Entity sets: This type of data source could be, for example, a table such as
Customers. - Other queries: Queries in LightSwitch are composable. This means that you can create queries based on other queries.
- Navigation properties: When working on screens or in code, you can create queries based on navigation properties.
Now let’s look at the way in which data is returned from the server to the client. In Chapter 1, you saw how each data source in your application has a corresponding data service that runs on the server. When you create a query in LightSwitch, a query operation is defined in the data service. This query operation is exposed as a RIA service endpoint. The LightSwitch client executes a query by calling the query operation method via RIA services. This returns a set of entities from an entity set in the data service. A very important thing to note is that every query returns entities from an entity set. This means that entities are the units of data that you’ll actually work with. Single or multiple results can be returned, and the results can be optionally filtered or sorted. You can also define parameters, which allow you to pass in arguments that can then be used in your query.
Figure 6-1 shows an example of a user-defined query that returns customers filtered by surname. In this example, the actual filtering and sorting is applied at the server. In LightSwitch, the IDataServiceQueryable object allows you to create queries that are executed on the server. You’ll discover more on how this works later in this chapter.
You can also query against locally cached data. In this instance, the execution takes place locally, not on the server. You might need to do this if you want to carry out a filter operation that isn’t supported by the data service. You’ll see an example of how to execute a local query later in this chapter.
When you’re using LINQ to write queries in code, it’s also important to understand that deferred execution takes place. This means that your query isn’t executed at the exact place where your query condition is specified in code. If you create a query based on an IDataServiceQueryable object, you’ll find an Execute method that you can call. However, this method doesn’t actually execute the query on the server. The query is executed only at the moment in time when you read the results. You might do this by enumerating over the results with a foreach loop, or by calling a method such as ToArray or ToList.
The Data Returned from a Query
Because query operations return entities from an entity set, the shape of the query results cannot be modified. For example, a query could return a single customer or a collection of customers. Alternatively, it could return a single order or a collection of orders. However, a query cannot be used to return just the firstname property from a set of customers, nor can it be used to return some sort of combined object that is created by joining customers and orders (you’d need a RIA service to be able do that). This behavior might seem strange at first, particularly for Access or SQL developers who are accustomed to using select clauses to specify whatever columns they choose, or to return results that are joined to other tables.
At this point, you might wonder how to retrieve additional data that isn’t part of the underlying data set. As long as relationships have been defined between the entities, you can retrieve the related data later by using the navigation properties. Once you start thinking in the “LightSwitch way,” you’ll soon realize that it isn’t necessary to return joined data from queries.
Default Queries Created by LightSwitch
For each table in your application, LightSwitch automatically generates two queries called Single and All. If you have a table called Customer, LightSwitch generates a query called Customers_All to return all customers, and a query called Customers_Single to return a single instance of a customer by ID value. Chapter 4 shows how these queries can be called in code.
By default, any user-defined entity-set query that you create is based on LightSwitch’s autogenerated _All query. As mentioned earlier, LightSwitch queries are composable, meaning that you can create queries that are based on other queries. The root query for all user-defined queries is the _All query. When you base a query on another query, LightSwitch merges all of the underlying query expression operations into one. This expression is then applied at the server when the query is executed.
These default queries are also used when creating screens. If you create a screen based on a customer entity by using the Editable Grid Screen template, the screen uses the Customers_All query by default. Likewise, a customer screen based on the Details Screen template uses the Customers_Single query by default.
These queries are also shown when adding data items to a screen, as shown in Figure 6-2.
Screen Queries
If you create a screen based on a collection of data (an editable grid screen or search screen, for example), you can define additional parameters, filtering, and ordering at the screen level. To do this, click the Edit Query link and use the options available through the graphical designer, as shown in Figure 6-3.
A disadvantage of this approach is that changes are applied only to the screen and cannot be reused on other screens. Furthermore, it is not possible to customize the query further by writing code.
Managing Included Data in Screen Queries
When you design a screen query, you’ll find an option to manage the included data. This allows you to optimize the performance of your screen by choosing to exclude navigation properties.
Let’s imagine that you’ve created a screen based on an Order entity. In the screen designer, click the Edit Query link (just as if you were about to apply some custom filtering or sorting). When the query designer appears, view the properties of the query. You’ll see a link that is titled Manage Included Data. When you click this link, as shown in Figure 6-4, a dialog box appears that allows you to choose the child records that you want to return in your query.
Entity Set Queries
Entity set queries can be used on multiple screens and can be further customized by using code. You can create these queries by right-clicking an entity in Solution Explorer and choosing the Add Query menu option, as shown in Figure 6-5.
When you create a query in this way, LightSwitch uses the default All query as the data source. After creating your query, you can change the underlying data source. To do this, use the Source drop-down box in the title area of the query designer, shown in Figure 6-6. LightSwitch allows you to select only from other queries that return the same entity set.
After adding a query, you can set various settings by using the properties pane, shown in Figure 6-7.
These settings are as follows:
- Name: The name is used to uniquely identify the query in Solution Explorer. When you create a new screen, the Add New Screen dialog shows the query name in the list of available data sources that you can base your screen on.
- Number of Results Returned: You can choose either One or Many from the list of available options in the drop-down box.
- Description: The description can be used to add a comment about the query at design time. It is not exposed elsewhere in LightSwitch during design time nor runtime.
- Display Name: On a screen, the display name is used to identify the query in the Query Source drop-down of an entity property, shown in Figure 6-8.
The Number of Results Returned option is one of the main settings. By default, new queries are set to return many records.
Singleton queries can be created by selecting the One option. These queries return a single record or null. If you designate a query as a singleton query and write a query that returns more than one record, a compile-time error will not be generated. However, an exception will be thrown at runtime when you attempt to run the query. Singleton queries are not composable. This means that further queries cannot be based on a singleton query, and explains why only queries that return collections can be modified in the screen editor.
The Number of Results Returned option also specifies where the query is shown in the Add New Screen dialog box. Singleton queries can be used to create screens based on the New Data Screen and Details Screen templates. Queries that return multiple records can be used to create screens based on the Editable Grid Screen, List and Details Screen, and Search Data Screen templates, as shown in Figure 6-9.
Filtering and Sorting Data by Using the Designer
Now that you’ve added your entity set query, you can start to apply filter and sort conditions. You can do this either through code or by using the graphical designer. In this section, we’ll show you how to use the graphical designer.
Filtering Query Data
You can use the graphical designer to create simple filters in your queries. Figure 6-10 illustrates the elements of the query designer that you would use to filter data.
The first query drop-down allows you to select the type of query. Choosing the Where option creates a query that includes the records that match your query condition. The Where Not option excludes records that match your query condition.
If you’ve created multiple filters, you can organize your filters into groups. You can then apply operators between your filter groups, as shown in Figure 6-11. Applying the following operators between groups is similar to the way that you would parenthesize groups of conditions in a SQL WHERE clause:
- And
- Or
- And Not
- Or Not
For each specific filter, the query Operator drop-down list allows you to select from a list of operators that are shown in Table 6-2. Some of these operators will depend on the data type of the selected property, and whether the property is defined as a required property. For example, you can’t filter for records with null customer surnames if a surname is required.
The Comparison Type drop-down allows you to select the type of value that you want to filter against. You can choose from one the following four options (which are explained in the following section in more detail):
- Literal
- Property
- Parameter
- Global
Finally, the Comparison Value control allows you to enter the value that you want to use. The control shown to you varies, depending on the operator or comparison type chosen.
Comparing Values
The key part to creating a filter is to select a comparison type and to enter a value. In this section, we’ll examine the four comparison types that you can choose from.
Using Literal Values
The Literal option allows you to filter by a hard-coded value. First, select the data property that you want to use by using the Selection Property drop-down. You can then type in the value that you want to compare against by using the Comparison Value text box. If you choose the Is Between operator, two text boxes are shown to allow you to enter To and From values.
When using the Selection Property drop-down list, you can choose to filter by related records. Figure 6-12 shows a query that filters the OrderStatusID property by using a literal value of 1.
Using Properties
The Comparison Type drop-down list allows you to compare by property. This enables you to compare two fields in the same record. After choosing the Property option, a second drop-down box appears that allows you to specify the comparison property.
Figure 6-13 shows an example of how you would use a property comparison. It illustrates a query on a Project table that returns records for which the target finish date exceeds the actual finish date.
Using Parameters
Rather than using a hard-coded literal value, queries can be made more reusable by choosing the Parameter option from the Comparison Type drop-down list. After selecting the Parameter option, a second drop-down box appears that allows you to create a new parameter. If you choose to create a new parameter, the parameter appears in the Parameter section in the lower section of the query designer.
Parameters can be made optional by selecting the Is Optional check box, shown in the properties window of the parameter. If the value of the optional parameter is not set at runtime, the filter that uses the parameter is omitted. For example, an optional parameter might be used on a query that searches customers filtered by surname. If the parameter value is not set, all customer records are returned by the query because the filter clause is not applied. If a surname value is supplied, the filtering takes place as expected.
Optional parameters are most useful when designing search screens with one or more search filters. If the user doesn’t specify a value, a null value is passed to the query parameter. This allows LightSwitch to optimize performance by omitting the associated WHERE condition in the SQL query that is generated.
Using Parameterized Queries on Screens
Having created a query that filters by a parameter value, it’s important to know how to consume such a query. In this example, we’ll create a screen with an AutoCompleteBox that displays a list of available order status values. When the user selects an order status, the orders that match the selected order status value are shown on a data grid.
First, create a query on the Order table and add a parameter called OrderStatusID. Now apply a filter on the OrderStatusID property that matches the data value against the OrderStatusID parameter, as shown in Figure 6-14.
Next, save the query as OrdersByStatus and create an editable grid screen based on this query. By default, LightSwitch creates a local property and a text box that binds to the query parameter. Because we want to replace this default text box with an AutoCompleteBox, delete the property and text box from the screen.
The next step is to create an autocomplete box that displays the order statuses. To do this, add a query onto your screen that returns a list of all order statuses. In the screen designer, click the Add Data Item button. In the dialog box that appears, select the Query radio button. Now choose the OrderStatusSet option, and name the query OrderStatusProperty, as shown in Figure 6-15.
After creating the query, the OrderStatusProperty appears on the left side of the screen designer. Drag this into the middle section to create an AutoCompleteBox. The steps that have been carried out so far are shown in Figure 6-16.
Having created an AutoCompleteBox, the value that the user selects must be bound to the query parameter. Bring up the properties of the OrderStatusID parameter and change the parameter binding text box to OrderStatusProperty.OrderStatusID , as shown in Figure 6-17.
You can now run your project. When the screen is opened, you can use the AutoCompleteBox to control the items that are shown in the data grid (see Figure 6-18).
Using Global Values
When you create filters on Date or DateTime properties, LightSwitch allows you to filter by global values. These are predefined values that are calculated by LightSwitch.
Figure 6-19 illustrates the global variables that are shown when a Date property is selected. You can choose from a set of values that include Today, Start of Week, End of Week, and several others.
Later, you will learn how to create your own custom global variables that you can use in queries.
When filtering on DateTime properties, you need to be careful when using the Equals operator with the Today global value. This is because DateTime properties also include a time element.
Let’s suppose you have an Order table with an OrderDate property of data type DateTime. If you want to create a query to return all orders that were made today, creating a filter with the criteria OrderDate=Today would not work. This is because Today returns today’s date with a time of 12:00:00 a.m. To return all orders that were made today, you would need to use the Is Between operator and filter for orders between Today and End of Day.
Therefore, Start of Day is perhaps a better description for the value that is returned by the Today global value.
Sorting Query Data
The graphical query designer also allows you to apply multiple sort orders to a query. Figure 6-20 illustrates a query called CustomersSorted, which is based on a table called Customer.
The first drop-down list allows you to select a property from the table. The second option allows you to select a sort order; the available options are Ascending and Descending. After specifying a sort order, you can sort on additional properties by clicking the Add Sort button.
Using LINQ
Language Integrated Query (LINQ) allows you to write more-complex queries in LightSwitch. By using LINQ, you can apply filtering that you otherwise couldn’t achieve when using the graphical query designer. In LightSwitch, you can use LINQ in two main ways:
- After you create a query based on an entity set, you can use LINQ to apply further filtering before the results are returned.
- You can create your own queries in code, and use LINQ to retrieve the records that you need.
This section introduces you to LINQ, shows you how to construct a LINQ query, and explains how to use lambda expressions.
Why LINQ?
LINQ is a feature that was introduced in .NET 3.5. One of the goals of LINQ was to create a common query language to save you from having to learn multiple query languages. This is perfect for LightSwitch because it allows you to connect to many data sources, including SQL Server, SharePoint, or RIA data services.
LINQ allows you to use standard C# and VB syntax to describe query filter expressions and ordering. These query expressions can be translated into SQL (or whatever form the query provider supports) and executed remotely at the server.
LINQ also provides compile-time checking and IntelliSense support. For example, you could write a SELECT statement in SQL that attempts to retrieve a field that doesn’t exist. If you attempted to do something similar in LINQ, your code would not compile. Furthermore, the IntelliSense in LINQ simplifies development by suggesting the field names that you can use. Therefore, there’s no need for you to memorize the field names that you’ve used in your tables.
LINQ Syntax
To illustrate the syntax that makes up a LINQ query, Listing 6-1 shows an example. This code is attached to a screen button. When the button is clicked, a list of Order Status codes is shown to the user through a message box. The query contains a conditional statement so that only codes with an order status ID of less than 4 are returned.
Listing 6-1. Example of a Simple LINQ Query
VB:
File: ShipperCentralClientUserCodeOrderStatusCodeDetails.vb
Imports System.Text
Private Sub ShowOrderStatusCodes_Execute()
Dim message = New StringBuilder
Dim items = From dataItem In DataWorkspace.ApplicationData.OrderStatuses
Where dataItem.OrderStatusID < 4
Order By dataItem.StatusDescription
Select dataItem
For Each item As OrderStatus In items
message.AppendLine(
String.Format("{0} - {1}", item.OrderStatusID, item.StatusDescription))
Next
ShowMessageBox(message.ToString, "Order Status Codes", MessageBoxOption.Ok)
End Sub
C#:
File: ShipperCentralClientUserCodeOrderStatusCodeDetails.cs
using System.Text;
using System.Windows.Controls;
partial void ShowOrderStatusCodes_Execute()
{
StringBuilder message = new StringBuilder();
var items = from dataItem in DataWorkspace.ApplicationData.OrderStatuses
where dataItem.OrderStatusID < 4
orderby dataItem.StatusDescription
select dataItem;
foreach (OrderStatus item in items)
{
message.AppendLine(
String.Format("{0} - {1}", item.OrderStatusID, item.StatusDescription));
}
this.ShowMessageBox(message.ToString(), "Order Status Codes", MessageBoxOption.Ok);
}
Figure 6-21 shows the message box that appears when the code is run.
The parts that make up a LINQ query are shown in Figure 6-22.
Every LINQ query is based on a data source. In Listing 6-1 shown earlier, the data source used is the order status entity set. These data sources can be accessed by using the objects in the DataWorkspace object.
The dataItem variable is called the range variable. This behaves just like the iteration variable that is used in a foreach loop, but in LINQ, no actual iteration takes place in the query expression. When the query is executed, the range variable allows you to reference each element in the data source. This enables you to conditionally filter the results by using the Where method. If you don’t specify the data type of dataItem, the compiler can infer it. However, C# won’t allow query expression syntax over entity sets (described in the next section), so it’s worth explicitly specifying the type of the range variable.
The Select clause at the end of the statement specifies the shape, or type, of each returned element. If you were writing a query that returns a collection of customers, for example, the Select clause allows you to return just the first name and surname of each customer. However, it’s uncommon to do this in LightSwitch because most operations are designed to work around entities.
The results from a LINQ query can be a single object, multiple objects, or a subset of fields. These results are called a sequence and are of type IEnumerable<T> (or some other type that derives from this). This type is pronounced I enumerable of T, and T defines the data type. In this example, the items variable is of type IEnumerable<OrderStatus>. The data type of items is inferred by the compiler and is therefore not explicitly declared.
An important feature about LINQ is that query execution is deferred until the moment when you request the data. So in this example, the query is executed only when the foreach loop is run.
Two Types of Syntax
After you start learning LINQ, you’ll soon discover that you can use two types of syntax to express queries. Query syntax is commonly found in documentation and is generally more readable. However, the .NET Framework cannot natively understand query syntax and converts it into a series of method calls. This type of syntax is called method syntax. Rather than using query syntax, you could write a query in method syntax in the first place.
Query syntax is generally easier to read and write, particularly when creating queries that involve joins. However, certain operations can be performed only when using method syntax. Retrieving the element with the maximum value in a source sequence is an example of this.
To illustrate the differences between the two types of syntax, Listing 6-2 presents a query that returns all customers with the surname of Smith, sorted in ascending order by first name.
Listing 6-2. Illustration of Query Syntax and Method Syntax
VB:
'Example of Query Syntax
Dim items = From dataItem In DataWorkspace.ApplicationData.Customers
Where dataItem.Surname = "Smith"
Order By dataItem.Firstname
Select dataItem
'Example of Method Syntax
Dim items2 =
From dataItem In DataWorkspace.ApplicationData.Customers.Where(
Function(cust) cust.Surname = "Smith").OrderBy(
Function(cust) cust.Firstname)
Select dataItem
C#:
//Example of Query Syntax
var items =
from dataItem in DataWorkspace.ApplicationData.Customers
where dataItem.Surname == "Smith"
orderby dataItem.Firstname
select dataItem;
//Example of Method Syntax
var items2 =
from dataItem in DataWorkspace.ApplicationData.Customers
.Where(cust => cust.Surname == "Smith")
.OrderBy (cust => cust.Firstname )
select dataItem;
The data source for this query comes from a table called Customers. The actual output returned from both queries is identical. Don’t worry too much about the method syntax that is shown; this is explained shortly in the “Lambda Expressions” section.
Your decision to use either query syntax or method syntax often boils down to personal preference. For now, the important point is to realize that the two syntax types exist.
Joining Data
Unlike SQL, you rarely need to create joins when using LINQ with LightSwitch. Provided that relationships have been set up, the navigation properties that are defined at the entity allow you to access related data without having to create explicit joins in LINQ, as shown in Figure 6-23.
Furthermore, LightSwitch screens can consume only the entities that have been defined in your application. Therefore, there is little point in creating a query that returns a collection of customers joined with orders, because this is something that cannot be consumed by a LightSwitch screen.
Although you can write LINQ queries to explicitly create joins, we recommend that relationships are set up by using the table designer in the first place. This creates the navigation properties, which greatly simplifies the task of writing queries. If you haven’t set up relationships because you’re working across multiple data sources, or because you’re working on an external data source in which relationships have not been explicitly defined, remember that you can set up virtual relationships between tables. Chapter 3 explains how to do this.
Lambda Expressions
In Chapter 5, you saw some examples of using lambda expressions during the validation of data. Lambda expressions are unnamed, inline functions that you can use with other parts of LINQ. They help you to filter and sort your data by using very few lines of code.
When using method-syntax LINQ, lambda expressions are often passed in as arguments to standard query operator methods such as Where. When IntelliSense prompts you to pass an expression tree into a method, as shown in Figure 6-24, you should think about using lambda expressions.
Why Use Lambdas?
One of the great advantages of lambda expressions is that you can write terse expressions with very few lines of code. Another primary advantage is that query expressions can be sent to the server and executed remotely. This is all made possible by using the magic of LINQ expression trees. For example, let’s say that you’ve written a LINQ query on a LightSwitch screen. LightSwitch can pass the query expression that you’ve expressed as a lambda expression to the middle tier via RIA services. This query expression can then be passed to SQL Server, and the query expression is executed at the server. When writing a LINQ query, the Where method allows you to pass in a function delegate, rather than a lambda expression. If you choose to do this, the query expressions that you specify will not be executed remotely.
To demonstrate the advantages of using lambda expressions, we’ll write some code for searching out the first customer in a collection that matches the surname Smith.
An old-fashioned way of doing this is to write a foreach loop and to break when the customer is found. But that code can be improved by using query syntax LINQ, and improved even further by using lambda expressions, as shown in Listing 6-3.
Listing 6-3. Lambda Expression Sample
VB:
'1. For Each Loop Example – involves many lines of code
Dim foundCustomer As Customer = Nothing
For Each cust As Customer In DataWorkspace.ApplicationData.Customers
If cust.Surname = "smith" Then
foundCustomer = cust
Exit For
End If
Next
'2. Query Syntax LINQ
Dim foundCustomer =
(From cust in DataWorkspace.ApplicationData.customers
Where cust.Surname = "smith").FirstOrDefault()
'3. Method Syntax LINQ & Lamda Expression
Dim foundCustomer2 As Customer =
DataWorkspace.ApplicationData.Customers.Where(
Function(cust) cust.Surname = "smith").FirstOrDefault()
C#:
//1. For Each Loop Example – involves many lines of code
Customer foundCustomer = null;
foreach (Customer cust in DataWorkspace.ApplicationData.Customers)
{
if (cust.Surname == "smith")
{
foundCustomer = cust;
break;
}
}
//2. Query Syntax LINQ
Customer foundCustomer = (from cust in DataWorkspace.ApplicationData.Customers
where cust.Surname == "smith"
select cust).FirstOrDefault();
//3. Method Syntax LINQ & Lamda Expression
Customer foundCustomer2 =
DataWorkspace.ApplicationData.Customers.Where (
cust => cust.Surname == "smith").FirstOrDefault ();
Let’s take a look at the first example, which uses a foreach loop. The syntax is simple to understand. However, it requires many lines of code. More important, the code needs to retrieve all customers into the foreach loop in order to test each one locally. The query isn’t efficient because there isn’t any remote execution of the query expression.
The remaining LINQ examples are more efficient because the query expression is applied at SQL Server. Also, it’s important to note that the LINQ queries are executed only when the data is needed. So in this example, the LINQ queries are executed when the FirstOrDefault method is called.
Lambda Syntax
Let’s take a look at the lambda expression that has been used:
C# cust => cust.Surname == "Smith"
VB Function(cust) cust.Surname = "Smith"
Cust represents the lambda parameter. This represents each customer in the collection of customers that you’re working against. In this example, the implicit data-typing feature of .NET is used and saves you from having to explicitly define the data type of cust. The expression also could be written like this:
C# (Customer cust )=> cust.Surname == "Smith"
VB Function(cust as Customer) cust.Surname = "Smith"
In the C# version, the lambda parameter must be enclosed in brackets if implicit typing is not used. Brackets must also be used if you want to specify more than one lambda parameter. In C#, the => operator is called the lambda or goes to operator. This is used to separate the lambda parameter(s) from the actual expression.
In VB, the Function keyword is used to specify the lambda expression, and the lambda parameters are specified inside brackets following the Function keyword. It may seem strange for the Function keyword to be used without a function name being specified. For example, a typical function in VB looks like this:
Public Function GetSurname (cust as Customer) As String
As you can see, the function name here is called GetSurname. In our lambda expression code, a function name can be omitted because the syntax uses a feature of .NET 3.5 called anonymous methods. Prior to .NET 3.5, methods had to be explicitly named, and separate named methods would have to be written to perform the same task. The beauty of using anonymous methods is that it saves you from having to write all of this extra code.
If you wanted to, you could still choose to use a named function rather than an anonymous method. However, the query expressions that are specified like this can’t be executed remotely in the same way that a lambda expression can be. Therefore, the advantage of using lambda expressions with anonymous methods is that it allows queries to be executed more efficiently.
Finally, a conditional operation is applied by using the lambda parameter that returns true if the customer surname matches Smith. Because customer is a LightSwitch-generated entity, any navigation properties that you’ve defined on customer could also be used to construct a more complex query expression by referencing related records.
Standard Query Operators
The Where() function that is used in the preceding section is an example of an extension method. Extension methods allow you to add methods to an existing type without having to create a new derived type, or to modify and recompile the existing type.
Standard query operators in LINQ are extension methods on top of the IEnumerable<T> and IQueryable<T> types. There are about 50 of these standard operators. Appendix B provides a list.
Not of all of these query operators can be translated and executed remotely at SQL Server by LightSwitch. Therefore, LightSwitch uses IDataServiceQueryable to limit the set of extension methods to those that are known to be “remotable” through SQL Server and RIA services. These are shown in Table 6-3.
The Search operator is worth a mention. This lets you to pass in a search term, and finds matching records by searching across all string properties where the 'is searchable' setting has been set to true in the table designer. You’ll recognize that any screens created using the Search Data Screen template performs the search in the same way.
If you want to access the remaining LINQ operators, you can do so by casting your object to IEnumerable<T>. You can use the Cast<T>() or OfType<T> methods to do this, as shown in Figure 6-25. However, you should be aware that not all query operators are guaranteed to work. When writing client or common code, you should stick to the operators that are available on IDataServicequeryable.
Delegate Parameters
Several of the standard query operators expect a delegate parameter to be passed in (you would commonly pass in a lambda expression). The delegate parameter types that you’ll encounter will be one of three types:
A func delegate defines a method that optionally takes a set of parameters and returns a value. Func delegate parameters are frequently encountered in LightSwitch. The argument passed to the Where extension method is an example of Func delegate argument.
A predicate delegate is a function that returns true or false. The All and Any query operators both accept a predicate delegate as an argument. The purpose of the All operator is to return a Boolean that indicates whether all elements of a sequence match a condition (just to clarify, the All query operator should not be confused with the default _All query that was mentioned earlier). The Any method returns whether any element satisfies a condition. Creating an exists type query (a query that returns records for which related child records exist) is an example of where you would use the Any operator. This is demonstrated later in this chapter.
Action delegates are used rarely in querying, but we’ll mention them anyway to complete our discussion on arguments. Action delegates can include parameters but have no return value. If you use VB, think of an action delegate as a sub and a predicate delegate as a function that returns a Boolean. An example of a method that accepts an action delegate is the ForEach extension method on an object of type List<T>.
Listing 6-4 illustrates how this method can be used to build a string of customer surnames. Once done, the results are displayed to the user in a message box. This demonstration highlights how LINQ can be used to express foreach logic in just a single line of code.
Listing 6-4. Example of Passing In an Action Delegate
VB:
Dim customerSurnames As String = ""
Customers.ToList().ForEach(Function(item) customerSurnames += item.Surname)
ShowMessageBox(customerSurnames)
C#:
string customerSurnames = "";
Customers.ToList().ForEach(item => customerSurnames += item.Surname);
ShowMessageBox(customerSurnames);
This example is based on an editable grid screen of customers. The customer collection is of type VisualCollection<Customer>, and the ToList extension method coverts this to an object of type List<Customer>.
Outside of querying, the other common place you’ll find action delegates are in threading code. For example, to execute some code on the main UI dispatcher, you would call the Application.Current.Details.Dispatcher.BeginInvoke method and pass in an action delegate.
Where Is the Query Executed?
Queries can be executed in two ways: either locally on the client, or remotely at the server. In the case of user-defined (entity set) queries, the choice is simple: user-defined queries are executed on the server. Queries that are executed at the server pass through a query pipeline. You can refine the results that are returned in your query by writing LINQ code in the PreprocessQuery method. The important point here is that the PreprocessQuery method runs on the server, as part of a server-side query.
When writing a query in code, you can choose where your query is executed. The syntax that you use determines where the query execution takes place. This is summarized in Table 6-4. In the sample code shown, myOrder and myCustomer represent local screen properties.
In most cases, it’s preferable for queries to be executed remotely. However, it sometimes isn't possible to execute a query remotely, because the data service might not support a specific query operation. Alternatively, there might be some operation that you want to perform on locally cached data, rather than on the data on the server. The example shown later in this section provides an example of such a scenario.
In general, queries performed against an EntityCollection (a navigation property, for example) are executed locally. Queries performed against an EntitySet are executed remotely. To work out where the data is filtered, you can hover the mouse over an object in order to establish the data type, as shown in Figure 6-26.
The rest of this section examines the query pipeline, local execution, and remote execution in further detail. We'll also validate where the queries are actually executed by using SQL Profiler.
Query Pipeline
When you call a query in LightSwitch, the execution passes through the query pipeline. Just like the save pipeline (explained in Chapter 4), the query pipeline consists of phases that include points where you can inject your own custom server-side code. Figure 6-27 shows the phases in the query pipeline, and Table 6-5 describes the events that you can use to write custom code.
At the start of the query pipeline, you can write code in the CanExecute method to carry out security checks and to ensure that the user has sufficient permissions to access the data. You can find more details on writing authorization code in Chapter 14. LightSwitch also checks the CanRead status for the underlying entity set during this time.
At the Preprocessing phase, you can customize your query further by writing LINQ code. This allows you to append additional query operators, and to express more-complex query expressions beyond what can be achieved when using the LightSwitch designer.
During the Execution phase, LightSwitch transforms the query into one that the data provider understands. Examples of data providers are the ADO.NET Entity Framework provider for SQL Server, or the OData data provider for SharePoint. The query is then executed at the data store (for example, SQL Server), and the results returned to LightSwitch.
If the query succeeds, the Executed event is fired. If not, you can write code in the ExecuteFailed method to perform additional error handling if required.
Executing Queries on the Client
Entity collections and IEnumerables are used to execute queries locally. Navigation properties in LightSwitch return entity collections, and you can use these to execute queries locally. You can also call the Execute method on an IDataServiceQueryable object to return an IEnumerable. Here is an example:
DataWorkspace.ApplicationData.Orders.GetQuery().Execute()
This line returns an IEnumerable object that you can use to execute queries in code locally. This particular line returns all orders in the database and obviously has an impact on performance. Therefore, it’s important to exercise some caution when running code like this.
When you create a query that uses an entity collection as a data source, LightSwitch first executes the query remotely (on the server) to return results. These results are exposed to you as an IEnumerable. Any additional filtering then gets carried out locally.
Let’s examine a screen that contains a screen property called Customer. A navigation property called Orders allows you to see the related orders for the customer. The Customer property and navigation property in code are shown in Figure 6-28.
To prove that all of the related data is pulled down and filtered locally, we’ll create a button on a screen. This button executes the code shown in Listing 6-5 and returns orders with a shipped date of June 8, 2011.
Listing 6-5. Querying an Entity Collection
VB:
File: ShipperCentralClientUserCodeLocalQueryExample.vb
Private Sub GetEntityCollectionButton_Execute()
Dim items = From ord In Customer.Orders
Where ord.OrderDate = "2010-06-08"
Select ord
For Each ord In items
'code to consume the data would be added here
Next
End Sub
C#:
File: ShipperCentralClientUserCodeLocalQueryExample.cs
partial void GetEntityCollectionButton_Execute()
{
var items = from ord in Customer.Orders
where ord.OrderDate == DateTime.Parse("2010-06-08")
select ord;
foreach (Order ord in items)
{
//code to consume the data would be added here
}
}
When you access a navigation property for the first time, LightSwitch fills the entity collection by fetching all related records from the server. This process is known as eager loading. These results are then cached on the client. This explains why local queries can be performed over locally cached entities.
However, note that LightSwitch doesn’t fetch your data when you reference the Orders navigation property in your LINQ query. LINQ queries are deferred, and the data gets fetched and cached only when the code execution reaches the foreach loop. After the data has been retrieved, LightSwitch applies the filter locally to return only orders that match an OrderDate of June 8, 2011.
If you were to watch this operation being performed in SQL Profiler, you would see the results that are shown in Figure 6-29: LightSwitch selects all order records that are related to the customer shown onscreen. It does this by applying a WHERE clause that returns records that match a CustomerID of 1. The WHERE clause doesn’t apply a filter on the OrderDate. This proves that the date filtering isn’t carried out by SQL Server at the server, but is carried out by LightSwitch locally.
This local caching isn’t always ideal. For example, the size of the Customer.Orders can grow unbounded over time. If you want to work with a navigation property but don’t want the results to be cached, LightSwitch provides a Query property for each navigation property. This returns the underlying IDataServiceQueryable object for the navigation property, and you can use this to construct a query that is executed remotely. Behind the scenes, LightSwitch screens use the underlying IDataServiceQueryable query to perform pagination over navigation properties.
Local Query Example
The following example demonstrates a query that is executed locally. It provides a perfect example of an operation that cannot be performed remotely.
In this demonstration, we’ll create an order screen that allows line items to be entered. The order line items include a product name and quantity number. The example code combines any duplicate line items that are found and sums up the quantities. Figure 6-30 shows the screen that the user would see.
In Listing 6-6, the OrderLine navigation property is used to return an entity collection. This allows you to locally apply the grouping logic that is needed to find the duplicates. After the duplicates have been found, the remaining code sums up the product quantities and deletes the duplicates.
Listing 6-6. Local Query Example
VB:
File: ShipperCentralClientUserCodeDuplicateOrders.vb
Private Sub MergeLineItems_Execute()
Dim duplicates = From line In OrderProperty.OrderLine
Group line By line.Product Into prodGroup = Group, Count()
Where (prodGroup.Count > 1)
Select prodGroup
For Each dup In duplicates
Dim totalQty = dup.Sum(Function(line) line.Quantity)
Dim firstLine = dup.First()
firstLine.Quantity = totalQty
dup.Except(
New OrderLine() {firstLine}).ToList().ForEach(Sub(line) line.Delete())
Next
End Sub
C#:
File: ShipperCentralClientUserCodeDuplicateOrders.cs
partial void MergeLineItems_Execute()
{
var duplicates = from OrderLine line in this.Order.OrderLines
group line by line.Product into prodGroup
where prodGroup.Count() > 1
select prodGroup;
foreach (var dup in duplicates)
{
var totalQuantity = dup.Sum(line => line.Quantity);
var firstLine = dup.First();
firstLine.Quantity = totalQuantity;
dup.Except(
new OrderLine[] { firstLine }).ToList().ForEach(line => line.Delete());
}
}
Executing Queries on the Server
Entity sets and IDataServiceQueryable are used to build queries that run on the server. You can access entity sets through the DataWorkspace object, as shown here:
DataWorkspace.ApplicationData.Orders
If you want to query against a navigation property, LightSwitch exposes a Query method for each navigation property that is available. This returns an IDataServiceQueryable object that allows you to execute remote queries.
In Listing 6-7, we’ll modify our earlier example so that the query is executed on the server. In this example, the query method that we’ll use to return an IDataServiceQueryable is called OrdersQuery.
Listing 6-7. Using an IDataServiceQueryable to Query a Navigation Property on the Server
VB:
File: ShipperCentralClientUserCodeServerQueryExample.vb
Private Sub GetIDataServiceQueryableButton_Execute()
Dim items = From ord In Customer.OrdersQuery
Where ord.OrderDate = "2010-06-08"
Select ord
For Each ord In items
'code to consume the data would be added here
Next
End Sub
C#:
File: ShipperCentralClientUserCodeServerQueryExample.cs
partial void GetIDataServiceQueryableButton_Execute()
{
var items = from ord in Customer.OrdersQuery
where ord.OrderDate == DateTime.Parse("2010-06-08")
select ord;
foreach (Customer ord in items)
{
//code to consume the data would be added here
}
}
Figure 6-31 illustrates what is shown in SQL Server Profiler when the query is executed. As before, the LINQ query includes a query condition to return only orders that were made on June 8, 2010.
The SQL Profiler trace shows a WHERE clause that filters the results by CustomerID and OrderDate. In the previous example, the results were filtered only by CustomerID. This proves that all of the query conditions that are applied against an IDataServiceQueryable are executed remotely at the server.
Exploring Query Examples
This section presents some examples of queries. These examples are used to highlight typical problem scenarios that you might encounter.
Filtering by Related Child Items
If you create a query that is based on the Order table, the graphical designer allows you to filter your results by customer. In other words, you can very easily filter the results based on a parent record when using the graphical designer. Working in the other direction, however, a query based on the Customer table cannot be filtered by a property in the Order table. This is illustrated in Figure 6-32.
To filter by child items, create a query called CustomersWithOutstandingOrders. You’ll now need to write some custom code in the PreprocessQuery method. To do this, click the Write Code button in the query designer and choose the PreProcessQuery method option, as shown in Figure 6-33. This opens the code editor window, where you can enter the code, shown in Listing 6-8. Running the query then returns all customers with orders that are outstanding. The outstanding orders are indicated by an OrderStatusID of 1.
Listing 6-8. Filtering by Child Items
VB:
File : ShipperCentralServerUserCodeApplicationDataService.vb
Private Sub CustomersWithOutstandingOrders_PreprocessQuery(
ByRef query As IQueryable(Of Customer))
query = query.Where(
Function(custItem) custItem.Orders.Where(
Function(orderItem) orderItem.OrderStatus.OrderStatusID = 1).Any())
End Sub
C#:
File : ShipperCentralServerUserCodeApplicationDataService.cs
partial void CustomersWithOutstandingOrders_PreprocessQuery(
ref IQueryable<Customer> query)
{
query = query.Where
(item => item.Orders.Where(
orderItem => orderItem.OrderStatus.OrderStatusID == 1).Any());
}
Creating Exists/In Queries
This example demonstrates how to perform an exists or in query. This type of query returns records for which related child records exist. For example, this type of query can be used to return customers who have made an order. If you happen to be more conversant in SQL, Listing 6-9 provides a SQL translation to illustrate what this example strives to achieve.
Listing 6-9. SQL Equivilant of Example
SELECT CustomerID, Surname, Firstname
FROM Customers
WHERE CustomerID IN (
SELECT CustomerID FROM Orders)
This type of query is created in LightSwitch by writing code in the PreprocessQuery method. After creating a query called CustomersWithOrders, the code in Listing 6-10 returns all customers with orders.
Listing 6-10. Returning all Customers with Orders
VB:
File : ShipperCentralServerUserCodeApplicationDataService.vb
Private Sub CustomersWithOrders_PreprocessQuery(
ByRef query As IQueryable(Of Customer))
query = query.Where(Function(custItem) custItem.Orders.Any())
End Sub
C#:
File : ShipperCentralServerUserCodeApplicationDataService.cs
partial void CustomersWithOrders_PreprocessQuery(
ref IQueryable<Customer> query)
{
query = query.OrderBy(custItem => custItem.Orders.Any());
}
Creating Not Exists/Not In Queries
As a natural progression of the previous example, the next example shows how you would perform a not exists or not in query. This type of query returns records for which related child records do not exist.
Returning to the customer and orders example, this example illustrates a query for returning customers who have not made an order. Listing 6-11 shows the equivalent SQL that you would use to carry out this query.
Listing 6-11. SQL Equivalent of Not In Query
SELECT CustomerID, Surname, Firstname
FROM Customers
WHERE CustomerID NOT IN (
SELECT CustomerID FROM Orders)
To carry out this example, create a query called CustomersWithNoOrders. The code in Listing 6-12 can now be added into the PreprocessQuery method to return all customers without orders.
Listing 6-12. Returning all Customers without Orders
VB:
File : ShipperCentralServerUserCodeApplicationDataService.vb
Private Sub CustomersWithOrders_PreprocessQuery(
ByRef query As IQueryable(Of Customer))
query = query.Where(Function(custItem) Not custItem.Orders.Any())
End Sub
C#:
File : ShipperCentralServerUserCodeApplicationDataService.cs
partial void CustomersWithOrders_PreprocessQuery(
ref IQueryable<Customer> query)
{
query = query.OrderBy(custItem => !custItem.Orders.Any());
}
Filtering by Date Elements
The ability to filter by date elements is a common scenario. For example, you might want to create a screen that shows birthdays by showing everyone born on a given day and month.
As an alternative example, you might want to create a screen that returns all orders that were made in a given month and year. The following example illustrates how you would do this.
To allow the end user to enter a month and year, we’ll create a parameterized query called OrdersByMonthAndYear based on the Order table. Two parameters of data type Integer are created called OrderMonth and OrderYear, as shown in Figure 6-34.
The code in Listing 6-13 can now be added to the PreprocessQuery to return all orders matching the month and year that has been passed in.
Listing 6-13. Filtering by Month and Year Parameter Values
C#:
File : ShipperCentralServerUserCodeApplicationDataService.cs
partial void OrdersByMonthAndYear_PreprocessQuery(
int? OrderMonth, int? OrderYear, ref IQueryable<Order> query)
{
query = query.Where(
order => order.OrderDate.Month == OrderMonth.GetValueOrDefault(-1)
&& order.OrderDate.Year == OrderYear.GetValueOrDefault(-1));
}
VB:
File : ShipperCentralServerUserCodeApplicationDataService.vb
Private Sub OrdersByMonthAndYear_PreprocessQuery(
OrderMonth As System.Nullable(Of Integer),
OrderYear As System.Nullable(Of Integer),
ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Order))
query = query.Where(
Function(orderItem) orderItem.OrderDate.Month = OrderMonth.GetValueOrDefault(-1)
AndAlso orderItem.OrderDate.Year = OrderYear.GetValueOrDefault(1))
End Sub
Because the OrderMonth and OrderYear parameters are nullable, the GetValueOrDefault method converts null values to -1 or 1, respectively. This causes the query to return no records, rather than throw an exception when null values are passed into the query. A screen based on the Editable Grid Screen template can now be created by using this query. As with all other parameterized queries, LightSwitch automatically creates text boxes that allow the parameter values to be entered. The final screen is shown in Figure 6-35.
Top N Records
This final example shows you how to return the top n records. This example consists of two parts. In the first part, a query is created to return the top 10 most expensive products from a Product table. In the second part, a query is created on a Customer table to return the details of customers who have made the most recent orders.
To create a query that shows the top 10 most expensive products, a query called Top10ExpensiveProducts is created based on the Product table. Listing 6-14 shows the code that is written in the PreprocessQuery method to return the top 10 records.
Listing 6-14. Query to Return the Top 10 Most Expensive Products
VB:
File : ShipperCentralServerUserCodeApplicationDataService.vb
Private Sub Top10ExpensiveProducts_PreprocessQuery (
ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Product))
query = query.OrderByDescending(
Function(productItem) productItem.ProductPrice).Take(10)
End Sub
C#:
File : ShipperCentralServerUserCodeApplicationDataService.cs
partial void Top10ExpensiveProducts_PreprocessQuery (
ref IQueryable<Product> query)
{
query = query.OrderByDescending(productItem=> productItem.ProductPrice).Take(10);
}
The second query illustrates how you can return the top n records based on data in a related table. In this example, the number of records to return is parameterized and enables the end user to specify the number of records to show. First, create a query called CustomersWithMostOrders based on the Customer table. Next, create a parameter called TopN of data type Integer.
In the PreprocessQuery method, enter the code as shown in Listing 6-15.
Listing 6-15. Query to Return the Top 10 Most Expensive Products
VB:
File : ShipperCentralServerUserCodeApplicationDataService.vb
Private Sub CustomersWithMostOrders_PreprocessQuery(
TopN As System.Nullable(Of Integer),
ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Customer))
query = query.OrderByDescending(
Function(custItem) custItem.Orders.Count()).Take(TopN.GetValueOrDefault(1))
End Sub
C#:
File : ShipperCentralServerUserCodeApplicationDataService.cs
partial void CustomersWithMostOrders_PreprocessQuery(
int? TopN, ref IQueryable<Customer> query)
{
query = query.OrderByDescending(
custItem => custItem.Orders.Count()).Take(TopN.GetValueOrDefault(1));
}
This code orders the customer records by a count of related orders in descending order. Once again, the Take method is used, and this time around, the parameterized value is passed into this method rather than a hard-coded value.
Using Advanced Sorting Techniques
In this section, we’ll discuss some of the issues that relate to sorting. First, we’ll show you how users can sort data using the data grid control. We’ll also describe how this control can remember the sort sequences that have been applied between sessions. Second, we’ll show you how to sort your data by using related records.
Sorting the Data Shown in Grids
When data is displayed by using a LightSwitch grid, the end user can sort the columns by clicking the grid column headings. Clicking a column heading toggles the sort order between ascending and descending. An arrow in the column header indicates the sort order that is currently in use, as shown in Figure 6-36.
If a user applies a sort order by using the column header, the selected sort sequence is remembered between sessions. If the user exits out of the application, starts the application again, and reopens the same screen, the previous sort order is retained.
There is no way that a user can clear the grid sort order, and this can present developers with a problem. Let’s say that you want to show a grid of customers sorted by surname followed by first name. You’ve created an editable grid screen that is bound to a query that applies this sort order sequence.
When the user first opens the screen, the data is correctly sorted by surname followed by first name. If the user then applies a sort by clicking the Customer ID heading, there is no way for the user to return to the initial sort sequence of surname followed by first name. Because sort sequences are remembered between sessions, restarting the application does not fix the problem either.
The grid settings are saved on the file system along with other LightSwitch setting files. You can find these in the folder My DocumentsMicrosoftLightSwitchSettings. This folder contains a subfolder for every LightSwitch application that has been run on your computer. Listing 6-16 shows the files that are found in this folder. For each screen in your application, you will find a file with a .SortSettings extension. This is an XML file that contains the grid sort sequences that are remembered across sessions. The only way for a user to clear the sort settings is to delete this file.
Listing 6-16. File Listing of C:UsersTimDocumentsMicrosoftLightSwitchSettingsShipperCentral.1.0.0.0
Application.OutOfBrowser.WindowSettings
EditableCustomersSortedGrid.CustomersSortedDesc.SortSettings
EditableCustomersSortedGrid1.grid.ColumnSettings
StandardShell.NavigationView.Settings
StandardShell.RibbonCommandBar.Settings
If a user resizes the widths of the grid columns, these are also persisted between sessions in the .ColumnSettings file. The other files are used to retain the state of the application, navigation, and ribbon settings. Listing 6-17 shows the contents of the Application.OutOfBrowser.WindowSettings file and gives you a taste of what is contained in these various XML files. This file is used by LightSwitch to reopen your application in the same screen position as your last session.
Listing 6-17. Contents of Application.OutOfBrowser.WindowSettings
<?xml version="1.0" encoding="utf-8"?>
<OutOfBrowserWindowSettings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Top>10</Top>
<Left>360</Left>
<Width>1077</Width>
<Height>604</Height>
<WindowState>Normal</WindowState>
</OutOfBrowserWindowSettings>
Clearing a grid sort order by asking users to delete XML files is not a practical solution. If you need to create a grid screen based on a sorted query, the best compromise is to prevent users from setting their own sort orders by disabling sorting at a screen level. This is done by deselecting the Support Sorting check box at the entity collection level, as shown in Figure 6-37.
Sorting by Related Parent Records
In a typical customer/order system, a relationship is set up so that one customer can have many orders. Let’s imagine that you want to create a screen that displays order details ordered by customer name. It isn’t possible to perform this type of sort by using the graphical designer, because the related customer is not shown in the drop-down list of available sort fields, as you can see in Figure 6-38.
To implement a sort by a parent record, you need to write code in the PreprocessQuery method. Listing 6-18 illustrates the code that is used to do this.
Listing 6-18. Sorting by a Related Parent Item
VB:
File: ShipperCentralServerUserCodeApplicationDataService.vb
Private Sub OrdersSortedByCustomer_PreprocessQuery (
ByRef query As IQueryable(Of Order))
query = query.OrderBy(Function(order)order.Customer.Firstname)
End Sub
C#:
File: ShipperCentralServerUserCodeApplicationDataService.cs
partial void OrdersSortedByCustomer_PreprocessQuery(ref IQueryable<Order> query)
{
query = query.OrderBy(order => order.Customer.Firstname);
}
Sorting by Related Child Records
If you apply the previous example in the opposite direction, another scenario that you might want to perform is to sort by related child records. For example, you might want to display a grid of customers to highlight those who have recently made orders. The customer who has made the most recent order should appear at the top of the list.
Again, you would write code in the PreprocessQuery method to apply the sort sequence, as shown in Listing 6-19.
Listing 6-19. Sorting by Child Items
VB:
File: ShipperCentralServerUserCodeApplicationDataService.vb
Private Sub CustomersSortedByOrder_PreprocessQuery(
ByRef query As IQueryable(Of Customer))
query = query.OrderBy(
Function(custItem) custItem.Orders.Max(Function(ordItem) ordItem.OrderDate))
End Sub
C#:
File: ShipperCentralServerUserCodeApplicationDataService.cs
partial void CustomersSortedByOrder_PreprocessQuery(ref IQueryable<Customer> query)
{
query = query.OrderBy(
custItem => custItem.Orders.Max(ordItem => ordItem.OrderDate));
}
This example uses the Max query operator. It’s worth noting that the set of supported query operators are dependent on the back-end data source. For SQL Server, the Entity Framework can translate most (if not all) well-known operators. However, you may discover limitations when using other data sources such as SharePoint or a custom RIA data source. The only simple way to find out whether a query operator is supported is to try it and see if it succeeds.
Creating User-Defined Global Values
Earlier in this chapter, you saw some of the built-in global values that can be used when filtering on properties based on the Date and DateTime data types. It’s possible for you to create your own global values, and this would be ideal when creating filters that need to reference some other calculated value. For example, you could extend the date/time global values to include values such as 30 days ago, 30 days from now, or some other calculated date value.
By default, a set of global values are available for the Date and DateTime types. However, there is nothing to prevent you from creating global values for other data types.
The task of creating a global value includes two parts:
In the example that follows, a new global value is created that returns the name of the currently logged-on user. The name of the global value that we’ll create will be called LoggedOnUser.
Editing the LSML
The ApplicationData.lsml file needs to be edited to include a definition for the global value that you’re about to create. The file can be found in the Data folder for your project and can be edited by using a text editor such as Notepad. Chapter 2 includes a section on how to edit this file. As mentioned earlier, we highly recommend that you close out of Visual Studio and make a backup copy of the LSML file before editing.
After opening this file, find the XML root ModelFragment element and add the GlobalValueContainerDefinition element, as shown in Listing 6-20. The code in the listing can be added just before the </ModelFragment> tag, which generally appears at the very end of the file.
Listing 6-20. Global Value Definition to Add to the LSML
<?xml version="1.0" encoding="utf-8" ?>
<ModelFragment xmlns="http://schemas.microsoft.com/LightSwitch/2010/xaml/model"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">
<GlobalValueContainerDefinition Name="GlobalStrings">
<GlobalValueDefinition Name="LoggedOnUser" ReturnType=":String">
<GlobalValueDefinition.Attributes>
<DisplayName Value=" Logged On User " />
<Description Value ="Gets the currently logged on user." />
</GlobalValueDefinition.Attributes>
</GlobalValueDefinition>
</GlobalValueContainerDefinition>
The GlobalValueContainerDefinition defines a container for GlobalValueDefinition elements. The GlobalValueDefinition element defines the global variable that we’re about to create, and multiple GlobalValueDefinition elements can be specified in each GlobalValueContainerDefinition. The ReturnType element specifies that you’re creating a global value for the data type string. The other data types that you could use are as follows:
:String:Binary:Boolean:Int32:Decimal:Double
When editing the LSML, note that these values are case sensitive, so the LightSwitch IDE will not load the project if the case is incorrect.
Creating the Class
The next thing to do is to write the code that does the computation. In this example, the logged-on user is found by calling Application.Current.User.Name.
In Solution Explorer, switch to file view and create a new class in the common project. An ideal place to create this class is in the UserCode folder, if it exists. This folder is automatically created by LightSwitch if, for example, some custom validation code has been written for an entity.
The name of the class needs to match the name that is defined in the GlobalValueContainerDefinition element (GlobalStrings in this example). Now enter the code as shown in Listing 6-21.
Listing 6-21. Code to Return Logged-On User
VB:
File: ShipperCentralCommonUserCodeGlobalStrings.vb
Namespace LightSwitchApplication
Public Class GlobalStrings ' The name of your GlobalValueContainerDefinition
Public Shared Function LoggedOnUser() As String
' LoggedOnUser matches the name of the GlobalValueDefinition
Return Application.Current.User.Name
End Function
End Class
End Namespace
C#:
File: ShipperCentralCommonUserCodeGlobalStrings.cs
using System;
namespace LightSwitchApplication
{
public class GlobalStrings // The name of your GlobalValueContainerDefinition
{
public static String LoggedOnUser ()
// LoggedOnUser matches the name of the GlobalValueDefinition
{
return Application.Current.User.Name;
}
}
}
The new class contains VB shared or C# static methods for each GlobalValueDefinition element defined in your LSML. The methods that you create cannot contain any parameters. Furthermore, the namespace of the class must be set to LightSwitchApplication.
As you can see in this example, the new class contains a string function called LoggedOnUser.
Having created this method, your new global value is ready for use. If you create a new query and filter on a string property, the Logged On User global value appears in the drop-down box, as shown in Figure 6-39.
Note If you’re curious as to how the default DateTime global values work, the equivalent logic for the built-in global values can be found in the Microsoft.LightSwitch.RelativeDates namespace.
Summary
In this chapter, you’ve learned about queries in LightSwitch. The main topics covered in this chapter are as follows:
- The ways that queries can be defined in LightSwitch and how queries always return entity sets
- How to create queries and filters by using the query designer
- An introduction to LINQ, lambda expressions, and writing queries in code
- Examples of some advanced query scenarios
- How to create your own global values for use in your queries
Every query in LightSwitch returns entities from an entity set. Single or multiple entities can be returned. Because entities are returned from queries, the shape of the results cannot be modified. For example, you cannot add or remove properties (for example, table columns) from the output of a query. If you want to work with related data, you would use the navigation properties that are defined by the relationships in your data.
By default, LightSwitch creates two queries for returning all entities or a single entity by ID value. Queries are composable, meaning that they can be built on other queries.
You can create your own user-defined queries by using the graphical query designer. These queries use entity sets as their data source and are executed on the server. After creating a query, you can change the data source by using a drop-down box in the title section of the query designer.
Queries can also be defined at a screen level. When designing screens, you can modify the filtering and sorting of the underlying data. However, screen query definitions cannot be shared on other screens. Also, any queries that are created in this way cannot be extended by using code.
When creating user-defined or screen queries, you can easily filter and sort your data by using the graphical query designer. You can apply filters on your data by using a comparison type. These comparison types include Literal, Parameter, and Property. A literal comparison allows you to filter against a hard-coded value, and a property comparison allows you to compare one property against another in an entity. Parameterized queries allow you to pass arguments into a query and are ideal for creating queries that are reusable across multiple screens.
When queries are executed at the server, execution passes through a query pipeline. This consists of several phases, and you can inject your own custom code at various points. The PreprocessQuery method allows you to append additional query operators by using LINQ.
Queries in code are written using LINQ. LINQ allows you to express query operators by using familiar VB.NET or C# syntax. There are two flavors of LINQ: query syntax and method syntax. Query syntax looks very similar to SQL code and is easy to read. When compiled, the query syntax is converted into a series of method calls (method syntax). LINQ queries can be written using either query syntax or method syntax. However, certain operations can be performed only by using method syntax.
You rarely need to create joins when creating LINQ queries, because the navigation properties that you define on your entities allow you to reference related data.
LINQ queries can be executed on the client or on the server. This is determined by the data source and syntax that you use to define your query. LINQ queries against entity collections and navigation properties are executed locally.
Queries against entity sets and IDataServiceQueryable objects are executed remotely on the server. The query expressions that you specify by using LINQ and lambda expressions can be passed to the middle tier and executed by SQL Server.
You’ve also seen code that demonstrates some of the typical problem scenarios that you might encounter. This includes Exists and Not Exists queries, and queries to filter results by date elements.
When choosing to filter by a date property, LightSwitch includes built-in global values. These are calculated values such as Today, Start of Week, and Start of Month. In the last part of this chapter, you’ve seen how to create your own global values that you can use in your own queries.