How to: Retrieve Data from a Query by Using Code

 

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

You can retrieve queries from the model and then execute them in your code. This enables you to work with targeted collections of data in the business logic of your application.

For example, your model might contain a query named Products in Stock. To determine if a product is available, you can write validation code that retrieves the Products in Stock query, and then executes the query. After the query executes, your code can iterate over the resulting collection. If a product in this collection matches a product in the current sales order, the user can notify the customer about the delay.

You can also add code to narrow the results of a query by using a where clause. Using a where clause to narrow the results of a query can improve performance because the conditions of the where clause are applied on the server tier. For more information, see Queries: Retrieving Information from a Data Source.

Example: Retrieving Data from a Query and Iterating through the Results

The following helper method is called when a user adds a new line to a sales order. If you had an entity named Order_Details, you might call this method from the Order_Details_Inserting method.

This code retrieves the top ten customers based on sales orders by executing a query named TopNSalesOrders. If the ID of the customer who placed this order matches the ID of any customers returned by the query, a 10% discount is applied to the line item.

        private void Good_Customer_Discount(Order_Detail entity)
        {
            foreach (Customer cust in this.DataWorkspace.NorthwindData.
         TopNSalesOrders(10))
            {
                if (cust.CustomerID == entity.Order.Customer.CustomerID)
                {
                    entity.Discount = 0.1F;
                }
            }
        }

        Private Sub Good_Customer_Discount(ByVal entity As Order_Detail)
            For Each cust As Customer In _
                Me.DataWorkspace.NorthwindData.TopNSalesOrders(10)
                If cust.CustomerID = entity.Order.Customer.CustomerID Then
                    entity.Discount = 0.1
                End If
            Next

        End Sub

Example: Narrowing the Results of a Query by Applying a Where Clause

The following code can be used as an alternative to the previous example. This code applies a where clause to the TopNSalesOrders query and only returns a customer if the customer is placing the current order..

        private void Good_Customer_Discount2(Order_Detail entity)
        {
            IDataServiceQueryable<Customer> query;

            query = from myCustomer in this.DataWorkspace.NorthwindData.
                        TopNSalesOrders(10)
                    where myCustomer.CustomerID == entity.Order.Customer.CustomerID
                    select myCustomer;
                 
            if (query.SingleOrDefault() != null)
            {
                entity.Discount = 0.1F;
            }
        }
        }

        Private Sub Good_Customer_Discount2(ByVal entity As Order_Detail)
            Dim query As IDataServiceQueryable(Of Customer)
            query = From mycustomer In Me.DataWorkspace.NorthwindData.TopNSalesOrders(10)
                    Where mycustomer.CustomerID = entity.Order.Customer.CustomerID
                    Select mycustomer

            If Not IsNothing(query.SingleOrDefault) Then
                entity.Discount = 0.01
            End If

        End Sub

Next Steps

To learn how to design a query visually by using a designer, see How to: Design a Query by Using the Query Designer.

To learn how to extend a query in the model by using code, see How to: Extend a Query by Using Code.

See Also

Queries: Retrieving Information from a Data Source
How to: Add, Remove, and Modify a Query
Walkthrough: Designing a Screen
How to: Design a Query by Using the Query Designer
How to: Extend a Query by Using Code