How to: Query Related Objects in an EntityCollection
This topic provides examples of how to query related objects in an EntityCollection returned by the relationship navigation property.
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 the Model and Mapping Files. 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.
Example
This example loads the collection of SalesOrderHeader objects related to a specific contact, and then uses a LINQ expression to return a list of orders placed online that have already shipped.
' Specify the customer ID.
Dim customerId As Integer = 4332
Using context As New AdventureWorksEntities()
' Get a specified customer by contact ID.
Dim customer = (From customers In context.Contacts _
Where customers.ContactID = customerId _
Select customers).First()
' You do not have to call the Load method to load the orders for the customer,
' because lazy loading is set to true
' by the constructor of the AdventureWorksEntities object.
' With lazy loading set to true the related objects are loaded when
' you access the navigation property. In this case SalesOrderHeaders.
' Write the number of orders for the customer.
Console.WriteLine("Customer '{0}' has placed {1} total orders.", _
customer.LastName, customer.SalesOrderHeaders.Count)
' Get the online orders that have shipped.
Dim shippedOrders = From order In customer.SalesOrderHeaders _
Where order.OnlineOrderFlag = True AndAlso order.Status = 5 _
Select order
' Write the number of orders placed online.
Console.WriteLine("{0} orders placed online have been shipped.", shippedOrders.Count())
End Using
// Specify the customer ID.
int customerId = 4332;
using (AdventureWorksEntities context =
new AdventureWorksEntities())
{
// Get a specified customer by contact ID.
var customer = (from customers in context.Contacts
where customers.ContactID == customerId
select customers).First();
// You do not have to call the Load method to load the orders for the customer,
// because lazy loading is set to true
// by the constructor of the AdventureWorksEntities object.
// With lazy loading set to true the related objects are loaded when
// you access the navigation property. In this case SalesOrderHeaders.
// Write the number of orders for the customer.
Console.WriteLine("Customer '{0}' has placed {1} total orders.",
customer.LastName, customer.SalesOrderHeaders.Count);
// Get the online orders that have shipped.
var shippedOrders =
from order in customer.SalesOrderHeaders
where order.OnlineOrderFlag == true
&& order.Status == 5
select order;
// Write the number of orders placed online.
Console.WriteLine("{0} orders placed online have been shipped.",
shippedOrders.Count());
}
This example uses the same LINQ query as the first example against the collection of SalesOrderHeader objects. Instead of initially loading all related objects into the collection, the CreateSourceQuery method is used to load only the objects returned by the query. The Load method is then called on the EntityCollection returned by the SalesOrderHeader relationship navigation property to load the remaining related objects.
' Specify the customer ID.
Dim customerId As Integer = 4332
Using context As New AdventureWorksEntities()
' Get a specified customer by contact ID.
Dim customer = (From customers In context.Contacts
Where customers.ContactID = customerId
Select customers).First()
' Use CreateSourceQuery to generate a query that returns
' only the online orders that have shipped.
Dim shippedOrders = From orders In customer.SalesOrderHeaders.CreateSourceQuery() _
Where orders.OnlineOrderFlag = True AndAlso orders.Status = 5 _
Select orders
' Write the number of orders placed online.
Console.WriteLine("{0} orders placed online have been shipped.", shippedOrders.Count())
' You do not have to call the Load method to load the orders for the customer,
' because lazy loading is set to true
' by the constructor of the AdventureWorksEntities object.
' With lazy loading set to true the related objects are loaded when
' you access the navigation property. In this case SalesOrderHeaders.
' Write the number of total orders for the customer.
Console.WriteLine("Customer '{0}' has placed {1} total orders.", _
customer.LastName, customer.SalesOrderHeaders.Count)
End Using
// Specify the customer ID.
int customerId = 4332;
using (AdventureWorksEntities context =
new AdventureWorksEntities())
{
// Get a specified customer by contact ID.
var customer = (from customers in context.Contacts
where customers.ContactID == customerId
select customers).First();
// Use CreateSourceQuery to generate a query that returns
// only the online orders that have shipped.
var shippedOrders =
from orders in customer.SalesOrderHeaders.CreateSourceQuery()
where orders.OnlineOrderFlag == true
&& orders.Status == 5
select orders;
// Write the number of orders placed online.
Console.WriteLine("{0} orders placed online have been shipped.",
shippedOrders.Count());
// You do not have to call the Load method to load the orders for the customer,
// because lazy loading is set to true
// by the constructor of the AdventureWorksEntities object.
// With lazy loading set to true the related objects are loaded when
// you access the navigation property. In this case SalesOrderHeaders.
// Write the number of total orders for the customer.
Console.WriteLine("Customer '{0}' has placed {1} total orders.",
customer.LastName, customer.SalesOrderHeaders.Count);
}
See Also
Tasks
How to: Execute a Query that Returns Entity Type Objects
How to: Use Query Paths to Shape Results
How to: Navigate Relationships Using Navigation Properties