Exemplos de sintaxe da consulta com base em método: Adição a operadores
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