How to: Use Query Paths to Shape Results (Entity Framework)

This topic provides an example of how to specify a query path that defines which related objects are returned when a specific object is queried in an Entity Data Model (EDM). The query in this example returns a single Contact object and all of the related SalesOrderHeader and SalesOrderDetail objects. The same example is shown using each of the following Entity Framework query technologies:

• LINQ to Entities

• Entity SQL with ObjectQuery<T>

• Query builder methods of ObjectQuery<T>

The example in this topic is based on the Adventure Works Sales Model. To run the code in this example, you must have already added the AdventureWorks Sales Model to your project and configured your project to use the Entity Framework. To do this, complete the procedures in How to: Manually Configure an Entity Framework Project and How to: Manually Define an Entity Data Model (Entity Framework). You can also use the Entity Data Model Wizard to define the AdventureWorks Sales Model. For more information, see How to: Use the Entity Data Model Wizard (Entity Framework).

Example

This is the LINQ to Entities example.

Using context As New AdventureWorksEntities
    ' Define a LINQ query with a path that returns 
    ' orders and items for a contact.
    Dim contacts = (From contact In context.Contact _
        .Include("SalesOrderHeader.SalesOrderDetail") _
        Select contact).FirstOrDefault()

    Try
        ' Execute the query and display information for each item 
        ' in the orders that belong to the contact.
        For Each order As SalesOrderHeader In contacts.SalesOrderHeader
            Console.WriteLine(String.Format("PO Number: {0}", _
                order.PurchaseOrderNumber))
            Console.WriteLine(String.Format("Order Date: {0}", _
                order.OrderDate.ToString()))
            Console.WriteLine("Order items:")
            For Each item As SalesOrderDetail In order.SalesOrderDetail
                Console.WriteLine(String.Format("Product:{0}" _
                    & "Quantity: {1}", item.ProductID.ToString(), _
                    item.OrderQty.ToString()))
            Next
        Next
    Catch ex As EntitySqlException
        Console.WriteLine(ex.ToString())
    Catch ex As EntityCommandExecutionException
        Console.WriteLine(ex.ToString())
    End Try
End Using

using (AdventureWorksEntities context =
    new AdventureWorksEntities())
{
    // Define a LINQ query with a path that returns 
    // orders and items for a contact.
    var contacts = (from contact in context.Contact
                  .Include("SalesOrderHeader.SalesOrderDetail")
                  select contact).FirstOrDefault();

    try
    {
        // Execute the query and display information for each item 
        // in the orders that belong to the contact.
        foreach (SalesOrderHeader order in contacts
            .SalesOrderHeader)
        {
            Console.WriteLine(String.Format("PO Number: {0}",
                order.PurchaseOrderNumber));
            Console.WriteLine(String.Format("Order Date: {0}",
                order.OrderDate.ToString()));
            Console.WriteLine("Order items:");
            foreach (SalesOrderDetail item in order.SalesOrderDetail)
            {
                Console.WriteLine(String.Format("Product: {0} "
                    + "Quantity: {1}", item.ProductID.ToString(),
                    item.OrderQty.ToString()));
            }
        }
    }
    catch (EntitySqlException ex)
    {
        Console.WriteLine(ex.ToString());
    }
    catch (EntityCommandExecutionException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

This is the Entity SQL example.

Using context As New AdventureWorksEntities
    ' Define an object query with a path that returns 
    ' orders and items for a specific contact.              
    Dim queryString = _
        "SELECT VALUE TOP(1) Contact FROM " & _
        "AdventureWorksEntities.Contact AS Contact"

    ' Define the object query with the query string.
    Dim contactQuery As New ObjectQuery(Of Contact)(queryString, _
        context, MergeOption.NoTracking)

    Dim contact As Contact = _
    contactQuery.Include("SalesOrderHeader.SalesOrderDetail") _
        .FirstOrDefault()

    Try
        ' Execute the query and display information for each item 
        ' in the orders that belong to the first contact.
        For Each order As SalesOrderHeader In contact.SalesOrderHeader
            Console.WriteLine(String.Format("PO Number: {0}", _
                order.PurchaseOrderNumber))
            Console.WriteLine(String.Format("Order Date: {0}", _
                order.OrderDate.ToString()))
            Console.WriteLine("Order items:")
            For Each item As SalesOrderDetail In order.SalesOrderDetail
                Console.WriteLine(String.Format("Product:{0}" _
                    & "Quantity: {1}", item.ProductID.ToString(), _
                    item.OrderQty.ToString()))
            Next
        Next
    Catch ex As EntitySqlException
        Console.WriteLine(ex.ToString())
    Catch ex As EntityCommandExecutionException
        Console.WriteLine(ex.ToString())
    End Try
End Using

using (AdventureWorksEntities context =
    new AdventureWorksEntities())
{
    // Define an object query with a path that returns 
    // orders and items for a specific contact.              
    string queryString =
        @"SELECT VALUE TOP(1) Contact FROM " + 
        "AdventureWorksEntities.Contact AS Contact";

    // Define the object query with the query string.
    ObjectQuery<Contact> contactQuery = new ObjectQuery<Contact>(queryString, 
        context, MergeOption.NoTracking);
    
    Contact contact =
        contactQuery.Include("SalesOrderHeader.SalesOrderDetail")
        .FirstOrDefault();

    try
    {
        // Execute the query and display information for each item 
        // in the orders that belong to the first contact.
        foreach (SalesOrderHeader order in contact
            .SalesOrderHeader)
        {
            Console.WriteLine(String.Format("PO Number: {0}",
                order.PurchaseOrderNumber));
            Console.WriteLine(String.Format("Order Date: {0}",
                order.OrderDate.ToString()));
            Console.WriteLine("Order items:");
            foreach (SalesOrderDetail item in order.SalesOrderDetail)
            {
                Console.WriteLine(String.Format("Product: {0} "
                    + "Quantity: {1}", item.ProductID.ToString(),
                    item.OrderQty.ToString()));
            }
        }
    }
    catch (EntitySqlException ex)
    {
        Console.WriteLine(ex.ToString());
    }
    catch (EntityCommandExecutionException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

This is the query builder method example.

Using context As New AdventureWorksEntities
    ' Create an object query with a path that returns orders and items for a contact.
    Dim contact As Contact = _
        context.Contact.Include("SalesOrderHeader.SalesOrderDetail") _
        .FirstOrDefault()
    Try
        ' Execute the query and display information for each item 
        ' in the orders that belong to the returned contact.
        Dim order As SalesOrderHeader
        For Each order In contact.SalesOrderHeader
            Console.WriteLine(String.Format("PO Number: {0}", _
                    order.PurchaseOrderNumber))
            Console.WriteLine(String.Format("Order Date: {0}", _
                    order.OrderDate.ToString()))
            Console.WriteLine("Order items:")
            Dim item As SalesOrderDetail
            For Each item In order.SalesOrderDetail
                Console.WriteLine(String.Format("Product:{0}" _
                    & "Quantity: {1}", item.ProductID.ToString(), _
                    item.OrderQty.ToString()))
            Next
        Next
    Catch ex As EntitySqlException
        Console.WriteLine(ex.ToString())
    Catch ex As EntityCommandExecutionException
        Console.WriteLine(ex.ToString())
    End Try
End Using

using (AdventureWorksEntities context =
    new AdventureWorksEntities())
{
    // Define an object query with a path that returns 
    // orders and items for a specific contact.
    Contact contact =
        context.Contact.Include("SalesOrderHeader.SalesOrderDetail")
        .FirstOrDefault();
    
    try
    {
        // Execute the query and display information for each item 
        // in the orders that belong to the first contact.
        foreach (SalesOrderHeader order in contact
            .SalesOrderHeader)
        {
            Console.WriteLine(String.Format("PO Number: {0}",
                order.PurchaseOrderNumber));
            Console.WriteLine(String.Format("Order Date: {0}",
                order.OrderDate.ToString()));
            Console.WriteLine("Order items:");
            foreach (SalesOrderDetail item in order.SalesOrderDetail)
            {
                Console.WriteLine(String.Format("Product: {0} "
                    + "Quantity: {1}", item.ProductID.ToString(),
                    item.OrderQty.ToString()));
            }
        }
    }
    catch (EntitySqlException ex)
    {
        Console.WriteLine(ex.ToString());
    }
    catch (EntityCommandExecutionException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

See Also

Tasks

How to: Execute a Query that Returns an Entity Type (Entity Framework)
How to: Navigate Relationships Using Navigation Properties (Entity Framework)

Concepts

Shaping Query Results (Entity Framework)
Query Builder Methods (Entity Framework)
Entity SQL Overview

Other Resources

LINQ to Entities