Shaping Query Results (Entity Framework)

Article
03/06/2009

When you execute a query, only objects that are specifically requested in the query are returned. For example, when a query against the Adventure Works Sales Model returns Customer objects, by default the related SalesOrderHeader objects are not returned, even though there is a relationship between Customer and SalesOrderHeader. This behavior ensures that the application is always aware of the scope of the data that is returned from an object query. By default, the relationship objects that represent associations between entity types are always returned. When objects are generated based on the conceptual schema of the EDM, navigation properties are generated for entity objects at both ends of an association. These navigation properties return either an EntityReference on the "one" end of a one-to-one or many-to-one relationship or an EntityCollection on the "many" end of a one-to-many or many-to-many relationship. For more information, see Entity Data Model Relationships.

You can compose an Entity SQL or LINQ to Entities query that explicitly navigates these relationships by using navigation properties. For more information, see How to: Navigate Relationships Using Navigation Properties (Entity Framework). However, you do not need to navigate relationships explicitly in your query to shape query results. There are two other ways to extend the results of a query to also load referenced objects: you can specify query paths, or you can explicitly load related objects using navigation properties. To exercise even more control over the results, you can define a query path and then explicitly load only selected related objects.

When considering which option to use, be aware that there is a tradeoff between the number of requests against the database and the amount of data returned in a single query. Query paths define the graph of objects returned by a query. When you define a query path, only a single request against the database is required to return all objects defined by the path in a single result set. Explicitly loading objects requires multiple round-trips to the database and might require multiple active result sets, but the amount of data returned is limited to only the objects being loaded.

Defining a Query Path to Shape Query Results

To specify the query path, pass a string representation of the object graph to the Include method on the ObjectQuery. This path specifies which related objects to return when an object query is executed. For example, a query path defined on a query for Contact objects ensures that each related SalesOrderHeader and SalesOrderDetail will be returned. This is shown in the following queries that use LINQ to Entities, Entity SQL, and query builder methods.

LINQ to Entities

' 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()

// 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();

Entity SQL

' 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()

// 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();

Query builder methods

' 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()

// Define an object query with a path that returns 
// orders and items for a specific contact.
Contact contact =
    context.Contact.Include("SalesOrderHeader.SalesOrderDetail")
    .FirstOrDefault();

The following considerations apply when defining query paths:

Query paths can be used with query builder methods and LINQ queries.
When you call Include, the query path is only valid on the returned instance of ObjectQuery. Other instances of ObjectQuery and the object context itself are not affected.

Because Include returns the query object, you can call this method multiple times on an ObjectQuery to include objects from multiple relationships, as in the following example:

' Create a SalesOrderHeader query with two query paths, 
' one that returns order items and a second that returns the 
' billing and shipping addresses for each order.
Dim query As ObjectQuery(Of SalesOrderHeader) = _
    context.SalesOrderHeader.Include("SalesOrderDetail").Include("Address")

// Create a SalesOrderHeader query with two query paths, 
// one that returns order items and a second that returns the 
// billing and shipping addresses for each order.
ObjectQuery<SalesOrderHeader> query =
    context.SalesOrderHeader.Include("SalesOrderDetail").Include("Address");

Using query paths can result in complex commands being executed against the data source from seemingly simple object queries. This occurs because one or more joins are required to return related objects in a single query. This complexity is greater in queries against a complex EDM, such as an entity with inheritance or a path that includes many-to-many relationships. Use the ToTraceString method to see the command that will be generated by an ObjectQuery. For more information, see Object Queries (Entity Framework). When a query path includes too many related objects or the objects contain too much row data, the data source might not be able to complete the query. This occurs if the query requires intermediate temporary storage that exceeds the capabilities of the data source. When this occurs, you can reduce the complexity of the data source query by explicitly loading related objects.

For more information, see How to: Use Query Paths to Shape Results (Entity Framework).

To explicitly load related objects, you must call the Load method on the related end retuned by the navigation property. For a one-to-many relationship, call the Load method on EntityCollection, and for a one-to-one relationship, call the Load on EntityReference. This loads the related object data into the object context. When a query returns a collection of objects, you can enumerate through the collection and call the Load method to load the related objects for each object in the collection, such as each SalesOrderDetail object that belongs to a SalesOrderHeader object. In the following example, SalesOrderDetail objects are explicitly loaded for the specified SalesOrderHeader object:

' Load the items for the order if not already loaded.
If Not order.SalesOrderDetail.IsLoaded Then
    order.SalesOrderDetail.Load()
End If

// Load the items for the order if not already loaded.
if (!order.SalesOrderDetail.IsLoaded)
{
    order.SalesOrderDetail.Load();
}

Note

When you call the Load method during a foreach (C#) or For Each (Visual Basic) enumeration, Object Services tries to open a new data reader. This operation will fail unless you have enabled multiple active results sets by specifying multipleactiveresultsets=true in the connection string. For more information, see Using Multiple Active Result Sets (MARS) on MSDN. You can also load the result of the query into a List collection, which closes the data reader and enables you to enumerate over the collection to load referenced objects.

For more information, see How to: Explicitly Load Related Objects (Entity Framework).

Because the EntityCollection class implements the IEnumerable interface, you can use LINQ to query the collection of objects loaded into the EntityCollection returned by a navigation property. This works whether the objects are implicitly loaded into the object context by specifying a query path or explicitly loaded by calling the Load method.

Calling the CreateSourceQuery method on an EntityCollection enables you to query related objects without first loading objects into the collection. CreateSourceQuery returns an ObjectQuery that, when executed, returns the same set of objects as calling the Load method. Query builder methods can be applied to this object query to further filter objects loaded into the collection. For more information, see How to: Query Related Objects in an EntityCollection (Entity Framework).

An ObjectQuery returns EDM data as entity objects. However when a navigation property is included in the query projection, the ObjectQuery returns a nested DbDataRecord that contains the related objects. For more information, see How to: Navigate Relationships Using Navigation Properties (Entity Framework).

Shaping Query Results (Entity Framework)

Defining a Query Path to Shape Query Results

See Also

Concepts

Additional resources

Shaping Query Results (Entity Framework)

Defining a Query Path to Shape Query Results

Explicitly Loading Related Objects

Querying Related Objects

See Also

Concepts

Additional resources