(For more resources on this topic, see here.)

Querying in LightSwitch

The following figure is based on the one you may review on the link mentioned earlier and schematically summarizes the architectural details:

Each entity set has a default All and Single as shown in the entity Category. All entity sets have a Save operation that saves the changes.

As defined, the entity sets are queryable and therefore query operations on these sets are allowed and supported. A query (query operation) requests an entity set(s) with optional filtering and sorting as shown, for example, in a simple, filtered, and sorted query on the Category entity.

Queries can be parameterized with one or more parameters returning single or multiple results (result sets). In addition to the defaults (for example, Category*(SELECT All) and Category), additional filtering and sorting predicates can be defined. Although queries are based on LINQ, all of the IQueryable LINQ operations are not supported.

The query passes through the following steps (the pipeline) before the results are returned.

• Pre-processing
• CanExecute—called to determine if this operation may be called or not
• Executing—called before the query is processed
• Pre-process query expression—builds up the final query expression
• Execution—LightSwitch passes the query expression to the data provider
• for execution
• Post-processing
• Executed—after the query is processed but before returning the results
• ExecuteFailed—if the query operation failed

Querying a Single Entity

We will start off creating a Visual Studio LightSwitch project LSQueries6 using the Visual Basic Template as shown (the same can be carried out with a C# template). We will attach this application to the SQL Server Express server’s Northwind database and bring in the Products (table) entity.

We will create a screen EditableProductList which brings up all the data in the Products entity as shown in the previous screenshot.

The above screen was created using the Editable Grid Screen template as shown next with the source of data being the Products entity.

We see that the EditableProductList screen is displaying all columns including those discontinued items and it is editable as seen by the controls on the displayed screen. This is equivalent to the SQL query, Select * from Products as far as display is concerned.

Filtering and sorting the data

Often you do not need all the columns but only a few columns of importance for your immediate needs, which besides being sufficient, enormously reduces the cost of running a query. What do you do to achieve this? Of course, you filter the data by posing a query to the entity.

Let us now say, we want products listing ProductID, ProductName excluding the discontinued items. We also need the list sorted. In SQL Syntax, this reduces to:

SELECT [Product List].ProductID, [Product List].ProductName
FROM Products AS [Product List]
WHERE ((([Product List].Discontinued) =0))
ORDER BY [Product List].ProductName;

This is a typical filtering of data followed by sorting the filtered data.

Filtering the data

In LightSwitch, this filtering is carried out as shown in the following steps:

1. Click on Query menu item in the LSQueries Designer as shown:

   The Designer (short for Query Designer) pops-up as shown and the following changes are made in the IDE: A default Query1 gets added to the Products entity on which it is based as shown; the Query1 property window is displayed and the Query Designer window is displayed. Query1 can be renamed in its Properties window (this will be renamed as Product List). The query target is the Products table and the return type is Product.

   As you can see Microsoft has provided all the necessary basic querying in this designer. If the query has to be changed to something more complicated, the Edit Additional Query Code link can be clicked to access the ProductListDataService as shown:

   Well, this is not a SQL Query but a LINQ Query working in the IDE. We know that entities are not just for relational data and this makes perfect sense because of the known advantages of LINQ for queries (review the following link: http://msdn.microsoft.com/en-us/library/bb425822.aspx). One of those main advantages is that you can write the query inVB or C#, and the DataContext, the main player takes it to SQL and runs queries that SQL Databases understand. It’s more like a language translation for queries with many more advantages than the one mentioned.

2. Hover over Add Filter to review what this will do as shown:
   This control will add a new filter condition. Note that Query1 has been renamed (right-click on Query1 and choose Rename) to ProductList.

3. Click on the Add Filter button.
   The Filter area changes to display the following:

   The first field in the entity will come up by default as shown for the filtered field for the ‘Where’ clause. The GUI is helping to build up “Where CategoryID = “. However, as you can see from the composite screenshot (four screens were integrated to create this screenshot) built from using all the drop-down options that you can indeed filter any of the columns and choose any of the built-in criteria. Depending on the choice, you can also add parameter(s) with this UI.

4. For the particular SQL Query we started with, choose the drop-down as shown.

Notice that LightSwitch was intelligent enough to get the right data type of value for the Boolean field Discontinued. You also have an icon (in red, left of Where) to click on should you desire to delete the query.

5. Add a Search Data Screen using the previous query as the source by providing the following information to the screen designer (associating the ProductList query for the Screen Data).

This screen when displayed shows all products not discontinued as shown. The Discontinued column has been dragged to the position shown in the displayed screen.