Ejemplos de sintaxis de expresiones de consulta: Operadores de combinaciónQuery Expression Syntax Examples: Join Operators

La combinación es una operación importante de las consultas dirigidas a orígenes de datos que no tienen relaciones navegables entre ellos, como las tablas de bases de datos relacionales.Joining is an important operation in queries that target data sources that have no navigable relationships to each other, such as relational database tables. Una combinación de dos orígenes de datos es la asociación de objetos en un origen de datos con objetos que comparten un atributo común en el otro origen de datos.A join of two data sources is the association of objects in one data source with objects that share a common attribute in the other data source. Para obtener más información, consulte Standard Query Operators Overview.For more information, see Standard Query Operators Overview.

Los ejemplos de este tema muestran cómo usar el GroupJoin y Join métodos para consultar el modelo AdventureWorks Sales con la sintaxis de expresión de consulta.The examples in this topic demonstrate how to use the GroupJoin and Join methods to query the AdventureWorks Sales Model using query expression syntax. 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

GroupJoinGroupJoin

EjemploExample

El ejemplo siguiente realiza una GroupJoin en las tablas SalesOrderHeader y SalesOrderDetail para buscar el número de pedidos por cliente.The following example performs a GroupJoin over the SalesOrderHeader and SalesOrderDetail tables to find the number of orders per customer. Una combinación de grupo es el equivalente a una combinación externa izquierda, que devuelve cada elemento del primer origen de datos (izquierdo), incluso si no hay elementos correlacionados en el otro origen de datos.A group join is the equivalent of a left outer join, which returns each element of the first (left) data source, even if no correlated elements are in the other data source.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
    ObjectSet<SalesOrderDetail> details = context.SalesOrderDetails;

    var query =
        from order in orders
        join detail in details
        on order.SalesOrderID
        equals detail.SalesOrderID into orderGroup
        select new
        {
            CustomerID = order.SalesOrderID,
            OrderCount = orderGroup.Count()
        };

    foreach (var order in query)
    {
        Console.WriteLine("CustomerID: {0}  Orders Count: {1}",
            order.CustomerID,
            order.OrderCount);
    }
}
Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
    Dim details As ObjectSet(Of SalesOrderDetail) = context.SalesOrderDetails

    Dim query = _
        From order In orders _
        Group Join detail In details _
        On order.SalesOrderID _
        Equals detail.SalesOrderID Into orderGroup = Group _
        Select New With _
        { _
            .CustomerID = order.SalesOrderID, _
            .OrderCount = orderGroup.Count() _
        }

    For Each order In query
        Console.WriteLine("CustomerID: {0}  Orders Count: {1}", _
            order.CustomerID, order.OrderCount)
    Next
End Using

EjemploExample

En el ejemplo siguiente se realiza una operación GroupJoin en las tablas Contact y SalesOrderHeader para buscar el número de pedidos por contacto.The following example performs a GroupJoin over the Contact and SalesOrderHeader tables to find the number of orders per contact. Se muestran el recuento de pedidos y los identificadores para cada contacto.The order count and IDs for each contact are displayed.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<Contact> contacts = context.Contacts;
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
        from contact in contacts
        join order in orders
        on contact.ContactID
        equals order.Contact.ContactID into contactGroup
        select new
        {
            ContactID = contact.ContactID,
            OrderCount = contactGroup.Count(),
            Orders = contactGroup
        };

    foreach (var group in query)
    {
        Console.WriteLine("ContactID: {0}", group.ContactID);
        Console.WriteLine("Order count: {0}", group.OrderCount);
        foreach (var orderInfo in group.Orders)
        {
            Console.WriteLine("   Sale ID: {0}", orderInfo.SalesOrderID);
        }
        Console.WriteLine("");
    }
}
Using context As New AdventureWorksEntities
    Dim contacts As ObjectSet(Of Contact) = context.Contacts
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = _
        From contact In contacts _
            Group Join order In orders _
            On contact.ContactID _
            Equals order.Contact.ContactID Into contactGroup = Group _
            Select New With { _
                .ContactID = contact.ContactID, _
                .OrderCount = contactGroup.Count(), _
                .Orders = contactGroup.Select(Function(order) order)}

    For Each group In query
        Console.WriteLine("ContactID: {0}", group.ContactID)
        Console.WriteLine("Order count: {0}", group.OrderCount)

        For Each orderInfo In group.Orders
            Console.WriteLine("   Sale ID: {0}", orderInfo.SalesOrderID)
        Next

        Console.WriteLine("")
    Next
End Using

JoinJoin

EjemploExample

El ejemplo siguiente realiza una combinación en las tablas SalesOrderHeader y SalesOrderDetail para obtener los pedidos en línea del mes de agosto.The following example performs a join over the SalesOrderHeader and SalesOrderDetail tables to get online orders from the month of August.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
    ObjectSet<SalesOrderDetail> details = context.SalesOrderDetails;

    var query =
        from order in orders
        join detail in details
        on order.SalesOrderID equals detail.SalesOrderID
        where order.OnlineOrderFlag == true
        && order.OrderDate.Month == 8
        select new
        {
            SalesOrderID = order.SalesOrderID,
            SalesOrderDetailID = detail.SalesOrderDetailID,
            OrderDate = order.OrderDate,
            ProductID = detail.ProductID
        };

    foreach (var order in query)
    {
        Console.WriteLine("{0}\t{1}\t{2:d}\t{3}",
            order.SalesOrderID,
            order.SalesOrderDetailID,
            order.OrderDate,
            order.ProductID);
    }
}
Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
    Dim details As ObjectSet(Of SalesOrderDetail) = context.SalesOrderDetails

    Dim query = _
        From ord In orders _
        Join det In details _
        On ord.SalesOrderID Equals det.SalesOrderID _
        Where ord.OnlineOrderFlag = True _
                And ord.OrderDate.Month = 8 _
        Select New With _
        { _
            .SalesOrderID = ord.SalesOrderID, _
            .SalesOrderDetailID = det.SalesOrderDetailID, _
            .OrderDate = ord.OrderDate, _
            .ProductID = det.ProductID _
        }

    For Each ord In query
        Console.WriteLine("{0}" & vbTab & "{1}" & vbTab & "{2:d}" & vbTab & "{3}", _
            ord.SalesOrderID, _
            ord.SalesOrderDetailID, _
            ord.OrderDate, _
            ord.ProductID)
    Next
End Using

Vea tambiénSee also