Compartilhar via

How to: Extend a Query by Using Code

  • Artigo

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

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

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.

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

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.

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

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.

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.

See Also

Tasks

How to: Add, Remove, and Modify a Query

Other Resources

Queries: Retrieving Information from a Data Source

Getting Started with LINQ in C#

Getting Started with LINQ in Visual Basic

LINQ in Visual Basic