Exemplos de sintaxe de expressão de consulta: projeção
Os exemplos neste tópico demonstram como usar o método Select e as palavras-chave From … From … para consultar o Modelo de Vendas do AdventureWorks usando a sintaxe da expressão de consulta. From … From … é o equivalente baseado em consulta do método SelectMany. 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
Selecionar
Exemplo
O exemplo a seguir usa o método Select para retornar todas as linhas da tabela de Product e exibir os nomes de produto.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
IQueryable<Product> productsQuery = from product in context.Products
select product;
Console.WriteLine("Product Names:");
foreach (var prod in productsQuery)
{
Console.WriteLine(prod.Name);
}
}
Using context As New AdventureWorksEntities
Dim products As ObjectSet(Of Product) = context.Products
Dim productsQuery = _
From product In products _
Select product
Console.WriteLine("Product Names:")
For Each product In productsQuery
Console.WriteLine(product.Name)
Next
End Using
Exemplo
O exemplo a seguir usa Select para retornar somente uma sequência de nomes do produto.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
IQueryable<string> productNames =
from p in context.Products
select p.Name;
Console.WriteLine("Product Names:");
foreach (String productName in productNames)
{
Console.WriteLine(productName);
}
}
Using context As New AdventureWorksEntities
Dim products As ObjectSet(Of Product) = context.Products
Dim productNames = _
From p In products _
Select p.Name
Console.WriteLine("Product Names:")
For Each productName In productNames
Console.WriteLine(productName)
Next
End Using
Exemplo
O exemplo a seguir usa o método Select para projetar as propriedades Product.Name e Product.ProductID em uma sequência de tipos anônimos.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var query =
from product in context.Products
select new
{
ProductId = product.ProductID,
ProductName = product.Name
};
Console.WriteLine("Product Info:");
foreach (var productInfo in query)
{
Console.WriteLine("Product Id: {0} Product name: {1} ",
productInfo.ProductId, productInfo.ProductName);
}
}
Using context As New AdventureWorksEntities
Dim products As ObjectSet(Of Product) = context.Products
Dim query = _
From product In products _
Select New With _
{ _
.ProductId = product.ProductID, _
.ProductName = product.Name _
}
Console.WriteLine("Product Info:")
For Each productInfo In query
Console.WriteLine("Product Id: {0} Product name: {1} ", _
productInfo.ProductId, productInfo.ProductName)
Next
End Using
De ... De ... (SelectMany)
Exemplo
O exemplo a seguir usa From … From … (o equivalente do método SelectMany) para selecionar todos os pedidos onde TotalDue é menor que 500,00.
decimal totalDue = 500.00M;
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Contact> contacts = context.Contacts;
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
var query =
from contact in contacts
from order in orders
where contact.ContactID == order.Contact.ContactID
&& order.TotalDue < totalDue
select new
{
ContactID = contact.ContactID,
LastName = contact.LastName,
FirstName = contact.FirstName,
OrderID = order.SalesOrderID,
Total = order.TotalDue
};
foreach (var smallOrder in query)
{
Console.WriteLine("Contact ID: {0} Name: {1}, {2} Order ID: {3} Total Due: ${4} ",
smallOrder.ContactID, smallOrder.LastName, smallOrder.FirstName,
smallOrder.OrderID, smallOrder.Total);
}
}
Dim totalDue = 500D
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 _
From order In orders _
Where contact.ContactID = order.Contact.ContactID _
And order.TotalDue < totalDue _
Select New With _
{ _
.ContactID = contact.ContactID, _
.LastName = contact.LastName, _
.FirstName = contact.FirstName, _
.OrderID = order.SalesOrderID, _
.Total = order.TotalDue _
}
For Each smallOrder In query
Console.WriteLine("Contact ID: {0} Name: {1}, {2} Order ID: {3} Total Due: ${4} ", _
smallOrder.ContactID, smallOrder.LastName, smallOrder.FirstName, _
smallOrder.OrderID, smallOrder.Total)
Next
End Using
Exemplo
O exemplo a seguir usa From … From … (o equivalente do método SelectMany) para selecionar todos os pedidos feitos em 1º de outubro de 2002 ou posterior.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Contact> contacts = context.Contacts;
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
var query =
from contact in contacts
from order in orders
where contact.ContactID == order.Contact.ContactID
&& order.OrderDate >= new DateTime(2002, 10, 1)
select new
{
ContactID = contact.ContactID,
LastName = contact.LastName,
FirstName = contact.FirstName,
OrderID = order.SalesOrderID,
OrderDate = order.OrderDate
};
foreach (var order in query)
{
Console.WriteLine("Contact ID: {0} Name: {1}, {2} Order ID: {3} Order date: {4:d} ",
order.ContactID, order.LastName, order.FirstName,
order.OrderID, order.OrderDate);
}
}
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 _
From order In orders _
Where contact.ContactID = order.Contact.ContactID _
And order.OrderDate >= New DateTime(2002, 10, 1) _
Select New With _
{ _
.ContactID = contact.ContactID, _
.LastName = contact.LastName, _
.FirstName = contact.FirstName, _
.OrderID = order.SalesOrderID, _
.OrderDate = order.OrderDate _
}
For Each order In query
Console.WriteLine("Contact ID: {0} Name: {1}, {2} Order ID: {3} Order date: {4:d} ", _
order.ContactID, order.LastName, order.FirstName, _
order.OrderID, order.OrderDate)
Next
End Using
Exemplo
O exemplo a seguir usa From … From … (o equivalente do método SelectMany) para selecionar todos os pedidos onde o total do pedido é maior que 10000,00 e usa a atribuição From para evitar solicitar o total duas vezes.
decimal totalDue = 10000.0M;
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Contact> contacts = context.Contacts;
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
var query =
from contact in contacts
from order in orders
let total = order.TotalDue
where contact.ContactID == order.Contact.ContactID
&& total >= totalDue
select new
{
ContactID = contact.ContactID,
LastName = contact.LastName,
OrderID = order.SalesOrderID,
total
};
foreach (var order in query)
{
Console.WriteLine("Contact ID: {0} Last name: {1} Order ID: {2} Total: {3}",
order.ContactID, order.LastName, order.OrderID, order.total);
}
}
Dim totalDue = 10000D
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 _
From order In orders _
Let total = order.TotalDue _
Where contact.ContactID = order.Contact.ContactID _
And total >= totalDue _
Select New With _
{ _
.ContactID = contact.ContactID, _
.LastName = contact.LastName, _
.OrderID = order.SalesOrderID, _
total _
}
For Each order In query
Console.WriteLine("Contact ID: {0} Last name: {1} Order ID: {2} Total: {3}", _
order.ContactID, order.LastName, order.OrderID, order.total)
Next
End Using