Exemplos de sintaxe da consulta com base em método: Adição a operadores

Artigo
06/08/2023

Os exemplos neste tópico demonstram como usar os métodos Join e GroupJoin para consultar o Modelo de Vendas do AdventureWorks usando a sintaxe da consulta com base em método. O Modelo de vendas AdventureWorks usado nesses exemplos é criado a partir das tabelas Contact, Address, Product, SalesOrderHeader e SalesOrderDetail no banco de dados de exemplo AdventureWorks.

Os exemplos deste tópico usam as seguintes instruções using/Imports:

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

GroupJoin

Exemplo

O exemplo a seguir executa um GroupJoin nas tabelas SalesOrderHeader e SalesOrderDetail para localizar o número de pedidos por cliente. Um group join é o equivalente a um left outer join, que retorna cada elemento da primeira fonte de dados (esquerda), mesmo se nenhum elemento correlacionado estiver na outra fonte de dados.

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

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

Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
    Dim details As ObjectSet(Of SalesOrderDetail) = context.SalesOrderDetails

    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

Exemplo

O exemplo a seguir executa um GroupJoin nas tabelas Contact e SalesOrderHeader para localizar o número de pedidos por contato. A contagem do pedido e as identificações para cada contato são exibidos.

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

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

Join

Exemplo

O exemplo a seguir executa uma união sobre as tabelas de contatos e de SalesOrderHeader.

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

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

Using context As New AdventureWorksEntities
    Dim contacts As ObjectSet(Of Contact) = context.Contacts
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    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

Exemplo

O exemplo a seguir executa uma união sobre as tabelas de contatos e de SalesOrderHeader, agrupamento os resultados pela identificação de contatos

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

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

Using context As New AdventureWorksEntities
    Dim contacts As ObjectSet(Of Contact) = context.Contacts
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    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

Confira também

Consultas no LINQ to Entities

Compartilhar via

Exemplos de sintaxe da consulta com base em método: Adição a operadores

GroupJoin

Exemplo

Exemplo

Join

Exemplo

Exemplo

Confira também

Recursos adicionais