Ejemplos de sintaxis de consulta basada en métodos: operadores de combinación (LINQ to Entities)

Los ejemplos de este tema muestran cómo se utilizan los métodos Join y GroupJoin para consultar el modelo AdventureWorks Sales utilizando la sintaxis de consulta basada en métodos. 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.

Los ejemplos de este tema usan las siguientes instrucciones using/Imports:

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;

Para obtener más información, vea Cómo crear un proyecto de LINQ to Entities en Visual Studio.

GroupJoin

Ejemplo

El ejemplo siguiente realiza una GroupJoin en las tablas SalesOrderHeader y SalesOrderDetail para buscar el número de pedidos por cliente. 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.

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

    Dim query = orders.GroupJoin(details, _
            Function(order) order.SalesOrderID, _
            Function(detail) detail.SalesOrderID, _
            Function(order, orderGroup) 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

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

    var query = orders.GroupJoin(details, 
        order => order.SalesOrderID,
        detail => detail.SalesOrderID,
        (order, orderGroup) => new
        {
            CustomerID = order.SalesOrderID,
            OrderCount = orderGroup.Count()
        });

    foreach (var order in query)
    {
        Console.WriteLine("CustomerID: {0}  Orders Count: {1}",
            order.CustomerID,
            order.OrderCount);
    }

}

Ejemplo

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. Se muestran el recuento de pedidos y los identificadores para cada contacto.

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

    Dim query = contacts.GroupJoin(orders, _
            Function(contact) contact.ContactID, _
            Function(order) order.Contact.ContactID, _
            Function(contact, contactGroup) 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

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

    var query = contacts.GroupJoin(orders, 
        contact => contact.ContactID,
        order => order.Contact.ContactID,
        (contact, contactGroup) => new
        {
            ContactID = contact.ContactID,
            OrderCount = contactGroup.Count(),
            Orders = contactGroup.Select(order => order)
        });

    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("");
    }
    }

Join

Ejemplo

En el ejemplo siguiente se realiza una combinación en las tablas Contact y SalesOrderHeader.

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

    Dim query = _
        contacts.Join( _
            orders, _
            Function(ord) ord.ContactID, _
            Function(cont) cont.Contact.ContactID, _
            Function(cont, ord) New With _
                { _
                    .ContactID = cont.ContactID, _
                    .SalesOrderID = ord.SalesOrderID, _
                    .FirstName = cont.FirstName, _
                    .Lastname = cont.LastName, _
                    .TotalDue = ord.TotalDue _
                })

    For Each contact_order In query
        Console.WriteLine("ContactID: {0} " _
            & "SalesOrderID: {1} " & "FirstName: {2} " _
            & "Lastname: {3} " & "TotalDue: {4}", _
            contact_order.ContactID, _
            contact_order.SalesOrderID, _
            contact_order.FirstName, _
            contact_order.Lastname, _
            contact_order.TotalDue)
    Next
End Using

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

    var query =
        contacts.Join(
            orders,
            order => order.ContactID,
            contact => contact.Contact.ContactID,
            (contact, order) => new
            {
                ContactID = contact.ContactID,
                SalesOrderID = order.SalesOrderID,
                FirstName = contact.FirstName,
                Lastname = contact.LastName,
                TotalDue = order.TotalDue
            });

    foreach (var contact_order in query)
    {
        Console.WriteLine("ContactID: {0} "
                        + "SalesOrderID: {1} "
                        + "FirstName: {2} "
                        + "Lastname: {3} "
                        + "TotalDue: {4}",
            contact_order.ContactID,
            contact_order.SalesOrderID,
            contact_order.FirstName,
            contact_order.Lastname,
            contact_order.TotalDue);
    }
}

Ejemplo

En el ejemplo siguiente se realiza una combinación en las tablas Contact y SalesOrderHeader, agrupando los resultados por identificador de contacto.

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

    Dim query = _
        contacts.Join( _
            orders, _
            Function(ord) ord.ContactID, _
            Function(cont) cont.Contact.ContactID, _
            Function(cont, ord) New With _
                { _
                    .ContactID = cont.ContactID, _
                    .SalesOrderID = ord.SalesOrderID, _
                    .FirstName = cont.FirstName, _
                    .Lastname = cont.LastName, _
                    .TotalDue = ord.TotalDue _
                }) _
                .GroupBy(Function(record) record.ContactID)

    For Each group In query
        For Each contact_order In group
            Console.WriteLine("ContactID: {0} " _
                & "SalesOrderID: {1} " & "FirstName: {2} " _
                & "Lastname: {3} " & "TotalDue: {4}", _
                contact_order.ContactID, _
                contact_order.SalesOrderID, _
                contact_order.FirstName, _
                contact_order.Lastname, _
                contact_order.TotalDue)
        Next
    Next
End Using

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

    var query = contacts.Join(
        orders,
        order => order.ContactID,
        contact => contact.Contact.ContactID,
        (contact, order) => new
        {
            ContactID = contact.ContactID,
            SalesOrderID = order.SalesOrderID,
            FirstName = contact.FirstName,
            Lastname = contact.LastName,
            TotalDue = order.TotalDue
        })
            .GroupBy(record => record.ContactID);

    foreach (var group in query)
    {
        foreach (var contact_order in group)
        {
            Console.WriteLine("ContactID: {0} "
                            + "SalesOrderID: {1} "
                            + "FirstName: {2} "
                            + "Lastname: {3} "
                            + "TotalDue: {4}",
                contact_order.ContactID,
                contact_order.SalesOrderID,
                contact_order.FirstName,
                contact_order.Lastname,
                contact_order.TotalDue);
        }
    }
}

Vea también

Conceptos

Ejemplos de sintaxis de consulta basada en métodos (LINQ to Entities)