Ejemplos de sintaxis de consulta basada en métodos: Navegar por relacionesMethod-Based Query Syntax Examples: Navigating Relationships

Las propiedades de navegación de Entity FrameworkEntity Framework son propiedades de acceso directo que se emplean para localizar las entidades situadas en los extremos de una asociación.Navigation properties in the Entity FrameworkEntity Framework are shortcut properties used to locate the entities at the ends of an association. Las propiedades de navegación permiten a un usuario navegar de una entidad a otra, o desde una entidad a entidades relacionadas a través de un conjunto de asociaciones.Navigation properties allow a user to navigate from one entity to another, or from one entity to related entities through an association set. En este tema se ofrecen ejemplos de la sintaxis de consulta basada métodos para navegar por las relaciones a través de propiedades de navegación de las consultas de LINQ to EntitiesLINQ to Entities.This topic provides examples in method-based query syntax of how to navigate relationships through navigation properties in LINQ to EntitiesLINQ to Entities queries.

El modelo AdventureWorks Sales que se usa en estos ejemplos se crea a partir de las tablas Contact, Address, Product, SalesOrderHeader y SalesOrderDetail en la base de datos de ejemplo de AdventureWorks.The AdventureWorks Sales Model used in these examples is built from the Contact, Address, Product, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorks sample database.

Los ejemplos de este tema usan los siguientes using / Imports instrucciones:The examples in this topic use the following using/Imports statements:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Globalization;
using System.Data.EntityClient;
using System.Data.SqlClient;
using System.Data.Common;
Option Explicit On
Option Strict On
Imports System.Data.Objects
Imports System.Globalization

EjemploExample

En el ejemplo siguiente de la sintaxis de consulta basada en métodos se usa el método SelectMany para obtener todos los pedidos de los contactos cuyo apellido es "Zhou".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". La propiedad de navegación Contact.SalesOrderHeader se utiliza para obtener la colección de objetos SalesOrderHeader para cada contacto.The Contact.SalesOrderHeader navigation property is used to get the collection of SalesOrderHeader objects for each contact.

string lastName = "Zhou";
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    IQueryable<SalesOrderHeader> ordersQuery = context.Contacts
        .Where(c => c.LastName == lastName)
        .SelectMany(c => c.SalesOrderHeaders);

    foreach (var order in ordersQuery)
    {
        Console.WriteLine("Order ID: {0}, Order date: {1}, Total Due: {2}",
            order.SalesOrderID, order.OrderDate, order.TotalDue);
    }
}
Dim lastName = "Zhou"
Using context As New AdventureWorksEntities
    Dim ordersQuery = context.Contacts _
    .Where(Function(c) c.LastName = lastName) _
    .SelectMany(Function(o) o.SalesOrderHeaders)

    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

EjemploExample

En el ejemplo siguiente de sintaxis de consulta basada en métodos se utiliza el método Select para obtener todos los identificadores de contactos y la suma del importe total a pagar de cada contacto cuyo nombre sea "Zhou".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". La propiedad de navegación Contact.SalesOrderHeader se utiliza para obtener la colección de objetos SalesOrderHeader para cada contacto.The Contact.SalesOrderHeader navigation property is used to get the collection of SalesOrderHeader objects for each contact. El método Sum usa la propiedad de navegación Contact.SalesOrderHeader para sumar el importe total a pagar de todos los pedidos de cada contacto.The Sum method uses the Contact.SalesOrderHeader navigation property to sum the total due of all the orders for each contact.

string lastName = "Zhou";
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var ordersQuery = context.Contacts
        .Where(c => c.LastName == lastName)
        .Select(c => new
        {
            ContactID = c.ContactID,
            Total = c.SalesOrderHeaders.Sum(o => o.TotalDue)
        });

    foreach (var contact in ordersQuery)
    {
        Console.WriteLine("Contact ID: {0} Orders total: {1}", contact.ContactID, contact.Total);
    }
}
Dim lastName = "Zhou"
Using context As New AdventureWorksEntities
    Dim ordersQuery = context.Contacts _
    .Where(Function(c) c.LastName = lastName) _
    .Select(Function(c) New With _
                {.ContactID = c.ContactID, _
                .Total = c.SalesOrderHeaders.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

EjemploExample

En el ejemplo siguiente de la sintaxis de consulta basada en métodos se obtienen todos los pedidos de los contactos cuyo apellido es "Zhou".The following example in method-based query syntax gets all the orders of the contacts whose last name is "Zhou". La propiedad de navegación Contact.SalesOrderHeader se utiliza para obtener la colección de objetos SalesOrderHeader para cada contacto.The Contact.SalesOrderHeader navigation property is used to get the collection of SalesOrderHeader objects for each contact. El nombre y los pedidos del contacto se devuelven en un tipo anónimo.The contact's name and orders are returned in an anonymous type.

string lastName = "Zhou";
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var ordersQuery = context.Contacts
        .Where(c => c.LastName == lastName)
        .Select(c => new { LastName = c.LastName, Orders = c.SalesOrderHeaders });

    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("");
    }
}
Dim lastName = "Zhou"
Using context As New AdventureWorksEntities
    Dim ordersQuery = context.Contacts _
    .Where(Function(c) c.LastName = lastName) _
    .Select(Function(o) New With _
                {.LastName = o.LastName, _
                 .Orders = o.SalesOrderHeaders})

    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

EjemploExample

En el ejemplo siguiente se usan las propiedades de navegación SalesOrderHeader.Address y SalesOrderHeader.Contact para obtener la colección de los objetos Address y Contact asociados a cada pedido.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. El apellido del contacto, la dirección postal, el número de pedidos de venta y el importe total a pagar de cada pedido para la ciudad de Seattle se devuelven en un tipo anónimo.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.

string city = "Seattle";
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var ordersQuery = context.SalesOrderHeaders
        .Where(o => o.Address.City == city)
        .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("");
    }
}
Dim city = "Seattle"
Using context As New AdventureWorksEntities
    Dim ordersQuery = context.SalesOrderHeaders _
             .Where(Function(o) o.Address.City = city) _
             .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

EjemploExample

En el ejemplo siguiente se utiliza el método Where para buscar los pedidos que se realizaron después del 1 de diciembre de 2003 y, a continuación, se utiliza la propiedad de navegación order.SalesOrderDetail para obtener los detalles de cada pedido.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 (AdventureWorksEntities context = new AdventureWorksEntities())
{
    IQueryable<SalesOrderHeader> query =
        from order in context.SalesOrderHeaders
        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.SalesOrderDetails)
        {
            Console.WriteLine("  Product ID: {0} Unit Price {1}",
                orderDetail.ProductID, orderDetail.UnitPrice);
        }
    }
}
Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    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.SalesOrderDetails
            Console.WriteLine("  Product ID: {0} Unit Price {1}", _
                orderDetail.ProductID, orderDetail.UnitPrice)
        Next
    Next
End Using

Vea tambiénSee also