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

  1. Open a query in the Query Designer. For more information, see How to: Add, Remove, and Modify a Query.

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

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

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 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));
}

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.

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;
}

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

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft