Method-Based Query Syntax Examples: Navigating Relationships (LINQ to Entities)

Articolo
03/06/2009

Navigation properties in the Entity Data Model (EDM) are shortcut properties used to locate the entities at the ends of an association. Navigation properties allow a user to navigate from one entity to another, or from one entity to related entities through an association set. This topic provides examples in method-based query syntax of how to navigate relationships through navigation properties in LINQ to Entities queries.

The AdventureWorks Sales Model used in these examples is built from the Contact, Address, Product, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorks sample database.

The examples in this topic use the following using/Imports statements:

Option Explicit On
Option Strict On
Imports L2EExamplesVB.AdventureWorksModel
Imports System.Data.Objects
Imports System.Globalization

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using AdventureWorksModel;
using System.Globalization;

For more information, see How to: Create a LINQ to Entities Project in Visual Studio.

Example

The following example in method-based query syntax uses the SelectMany method to get all the orders of the contacts whose last name is "Zhou". The Contact.SalesOrderHeader navigation property is used to get the collection of SalesOrderHeader objects for each contact.

Using AWEntities As New AdventureWorksEntities
    Dim ordersQuery = AWEntities.Contact _
    .Where(Function(c) c.LastName = "Zhou") _
    .SelectMany(Function(o) o.SalesOrderHeader)

    For Each order In ordersQuery
        Console.WriteLine("Order ID: {0}, Order date: {1}, Total Due: {2}", _
                order.SalesOrderID, order.OrderDate, order.TotalDue)
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    IQueryable<SalesOrderHeader> ordersQuery = AWEntities.Contact
        .Where(c => c.LastName == "Zhou")
        .SelectMany(c => c.SalesOrderHeader);

    foreach (var order in ordersQuery)
    {
        Console.WriteLine("Order ID: {0}, Order date: {1}, Total Due: {2}",
            order.SalesOrderID, order.OrderDate, order.TotalDue);
    }
}

Example

The following example in method-based query syntax uses the Select method to get all the contact IDs and the sum of the total due for each contact whose last name is "Zhou". The Contact.SalesOrderHeader navigation property is used to get the collection of SalesOrderHeader objects for each contact. The Sum method uses the Contact.SalesOrderHeader navigation property to sum the total due of all the orders for each contact.

Using AWEntities As New AdventureWorksEntities
    Dim ordersQuery = AWEntities.Contact _
    .Where(Function(c) c.LastName = "Zhou") _
    .Select(Function(c) New With _
                {.ContactID = c.ContactID, _
                .Total = c.SalesOrderHeader.Sum(Function(o) o.TotalDue)})

    For Each order In ordersQuery
        Console.WriteLine("Contact ID: {0} Orders total: {1}", order.ContactID, order.Total)
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    var ordersQuery = AWEntities.Contact
        .Where(c => c.LastName == "Zhou")
        .Select(c => new
        {
            ContactID = c.ContactID,
            Total = c.SalesOrderHeader.Sum(o => o.TotalDue)
        });

    foreach (var contact in ordersQuery)
    {
        Console.WriteLine("Contact ID: {0} Orders total: {1}", contact.ContactID, contact.Total);
    }
}

Example

The following example in method-based query syntax gets all the orders of the contacts whose last name is "Zhou". The Contact.SalesOrderHeader navigation property is used to get the collection of SalesOrderHeader objects for each contact. The contact's name and orders are returned in an anonymous type.

Using AWEntities As New AdventureWorksEntities
    Dim ordersQuery = AWEntities.Contact _
    .Where(Function(c) c.LastName = "Zhou") _
    .Select(Function(o) New With _
                {.LastName = o.LastName, _
                 .Orders = o.SalesOrderHeader})

    For Each order In ordersQuery
        Console.WriteLine("Name: {0}", order.LastName)
        For Each orderInfo In order.Orders

            Console.WriteLine("Order ID: {0}, Order date: {1}, Total Due: {2}", _
                    orderInfo.SalesOrderID, orderInfo.OrderDate, orderInfo.TotalDue)
        Next

        Console.WriteLine("")
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    var ordersQuery = AWEntities.Contact
        .Where(c => c.LastName == "Zhou")
        .Select(c => new { LastName = c.LastName, Orders = c.SalesOrderHeader });

    foreach (var order in ordersQuery)
    {
        Console.WriteLine("Name: {0}", order.LastName);
        foreach (SalesOrderHeader orderInfo in order.Orders)
        {
            Console.WriteLine("Order ID: {0}, Order date: {1}, Total Due: {2}",
                orderInfo.SalesOrderID, orderInfo.OrderDate, orderInfo.TotalDue);
        }
        Console.WriteLine("");
    }
}

Example

The following example uses the SalesOrderHeader.Address and SalesOrderHeader.Contact navigation properties to get the collection of Address and Contact objects associated with each order. The last name of the contact, the street address, the sales order number, and the total due for each order to the city of Seattle are returned in an anonymous type.

Using AWEntities As New AdventureWorksEntities
    Dim ordersQuery = AWEntities.SalesOrderHeader _
             .Where(Function(o) o.Address.City = "Seattle") _
             .Select(Function(o) New With { _
                            .ContactLastName = o.Contact.LastName, _
                            .ContactFirstName = o.Contact.FirstName, _
                            .StreetAddress = o.Address.AddressLine1, _
                            .OrderNumber = o.SalesOrderNumber, _
                            .TotalDue = o.TotalDue _
             })

    For Each orderInfo In ordersQuery
        Console.WriteLine("Name: {0}, {1}", orderInfo.ContactLastName, orderInfo.ContactFirstName)
        Console.WriteLine("Street address: {0}", orderInfo.StreetAddress)
        Console.WriteLine("Order number: {0}", orderInfo.OrderNumber)
        Console.WriteLine("Total Due: {0}", orderInfo.TotalDue)
        Console.WriteLine("")
    Next

End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    var ordersQuery = AWEntities.SalesOrderHeader
        .Where(o => o.Address.City == "Seattle")
        .Select(o => new
        {
            ContactLastName = o.Contact.LastName,
            ContactFirstName = o.Contact.FirstName,
            StreetAddress = o.Address.AddressLine1,
            OrderNumber = o.SalesOrderNumber,
            TotalDue = o.TotalDue
        });

    foreach (var orderInfo in ordersQuery)
    {
        Console.WriteLine("Name: {0}, {1}", orderInfo.ContactLastName, orderInfo.ContactFirstName);
        Console.WriteLine("Street address: {0}", orderInfo.StreetAddress);
        Console.WriteLine("Order number: {0}", orderInfo.OrderNumber);
        Console.WriteLine("Total Due: {0}", orderInfo.TotalDue);
        Console.WriteLine("");
    }
}

Example

The following example uses the Where method to find orders that were made after December 1, 2003, and then uses the order.SalesOrderDetail navigation property to get the details for each order.

Using AWEntities As New AdventureWorksEntities
    Dim orders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader

    Dim query = _
        From order In orders _
        Where order.OrderDate >= New DateTime(2003, 12, 1) _
        Select order

    Console.WriteLine("Orders that were made after December 1, 2003:")
    For Each order In query
        Console.WriteLine("OrderID {0} Order date: {1:d} ", _
                order.SalesOrderID, order.OrderDate)
        For Each orderDetail In order.SalesOrderDetail
            Console.WriteLine("  Product ID: {0} Unit Price {1}", _
                orderDetail.ProductID, orderDetail.UnitPrice)
        Next
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<SalesOrderHeader> orders = AWEntities.SalesOrderHeader;

    IQueryable<SalesOrderHeader> query =
        from order in orders
        where order.OrderDate >= new DateTime(2003, 12, 1)
        select order;


    Console.WriteLine("Orders that were made after December 1, 2003:");
    foreach (SalesOrderHeader order in query)
    {
        Console.WriteLine("OrderID {0} Order date: {1:d} ",
            order.SalesOrderID, order.OrderDate);
        foreach (SalesOrderDetail orderDetail in order.SalesOrderDetail)
        {
            Console.WriteLine("  Product ID: {0} Unit Price {1}",
                orderDetail.ProductID, orderDetail.UnitPrice);
        }
    }
}

Condividi tramite

Method-Based Query Syntax Examples: Navigating Relationships (LINQ to Entities)

Example

Example

Example

Example

Example

See Also

Concepts

Risorse aggiuntive