How to: Extend a Query by Using Code
If you want to modify a query in ways that go beyond the capabilities of the query designer, you can extend the query by writing code. Visual Studio LightSwitch 2011 combines the conditions that you specify in the query designer with the conditions that you specify in your code to produce the query result. For more information, see Queries: Retrieving Information from a Data Source.
You cannot extend the query of a screen. You can only extend queries that appear in Solution Explorer. For more information about editing the query of a screen, see How to: Filter Data on a Screen.
To extend a query by using code
Open a query in the Query Designer. For more information, see How to: Add, Remove, and Modify a Query.
In the Properties window, click Edit Additional Query Code.
The server code file opens in the Code Editor. A method that represents your query appears in the code file. The name of the method starts with the name of your query and ends with the word _PreprocessQuery. For example, if the name of your query is GetCustomers, the name of the method that appears in Code Editor is GetCustomers_PreprocessQuery.
Customize the query by adding code to this method. Use LINQ syntax. For more information about how to write LINQ queries in Visual Basic, see Getting Started with LINQ in Visual Basic. For more information about how to write queries in C#, see Getting Started with LINQ in C#.
The following examples show two ways to modify a query by using code.
Top N Customers Based on Sales Orders
The following query returns customers who have placed the greatest number of orders. A parameter named TopN is passed into the method. The TopN parameter specifies the number of customers to return in the result.
Add parameters in the query designer.
Private Sub TopNSalesOrders_PreprocessQuery _ (ByVal TopN As System.Nullable(Of Short), _ ByRef query As System.Linq.IQueryable(Of LS_Queries_VB.Customer)) query = From myCustomer In query Where myCustomer.Orders.Count > 0 Select myCustomer Order By myCustomer.Orders.Count Descending Take (TopN) End Sub
You cannot design this query by using the query designer for the following reasons:
Orders appear on the many side of the customer-order relationship. The query designer prevents you from referencing related entities that have a multiplicity of many.
All Customers Who Purchased a Specific Product
The following query returns all customers who purchased a specific product by navigating several one-to-many relationships.
Private Sub CustomersWhoBoughtProduct_PreprocessQuery _ (ByVal ProductID As System.Nullable(Of Short), _ ByRef query As System.Linq.IQueryable(Of LS_Queries_VB.Customer)) query = From myCustomers In query From myOrders In myCustomers.Orders From myOrderDetails In myOrders.Order_Details Where myOrderDetails.Product.ProductID = ProductID Select Customers End Sub
You cannot design this query by using the query designer because the query designer prevents you from referencing related entities that have a multiplicity of many.
To learn how to design a query visually, see How to: Design a Query by Using the Query Designer.
To learn how to use a query in a screen, see How to: Filter Data on a Screen.
To learn how to run a query within custom code that you add to an application, see How to: Retrieve Data from a Query by Using Code.