How to: Extend a Query by Using Code

Article
03/27/2017

For the latest documentation on Visual Studio 2017, see Visual Studio 2017 Documentation.

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

Note

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 Silverlight 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#.

Examples

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.

Note

Add parameters in the query designer.

        partial void TopNSalesOrders_PreprocessQuery
            (short? TopN, ref IQueryable<Customer> query)
        {
            query = (from myCustomer in query
                     where myCustomer.Orders.Count() > 0
                     orderby myCustomer.Orders.Count() descending
                     select myCustomer).Take(System.Convert.ToInt16(TopN));
        }

        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.
The query counts orders by using the Count operation. The query designer does not support the use of operations such as Count, Aggregate, and Sum.

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.

        partial void CustomersWhoBoughtProduct_PreprocessQuery
            (short? ProductID, ref IQueryable<Customer> query)
        {
            query = from myCustomers in query
                    from myOrders in myCustomers.Orders
                    from myOrderDetails in myOrders.Order_Details
                    where myOrderDetails.Product.ProductID == ProductID
                    select myCustomers;
        }

        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.

Next Steps

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

Share via