Exemplos de sintaxe de expressão de consulta: filtragem
Os exemplos neste tópico demonstram como usar os Where
métodos e Where…Contains
para consultar o Modelo de Vendas AdventureWorks usando a sintaxe de expressão de consulta. Nota, onde...Contains
não pode ser usado como parte de uma consulta compilada.
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 neste tópico usam as seguintes using
/Imports
instruções:
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
Onde
Exemplo
O exemplo a seguir retorna todos os pedidos online.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var onlineOrders =
from order in context.SalesOrderHeaders
where order.OnlineOrderFlag == true
select new
{
SalesOrderID = order.SalesOrderID,
OrderDate = order.OrderDate,
SalesOrderNumber = order.SalesOrderNumber
};
foreach (var onlineOrder in onlineOrders)
{
Console.WriteLine("Order ID: {0} Order date: {1:d} Order number: {2}",
onlineOrder.SalesOrderID,
onlineOrder.OrderDate,
onlineOrder.SalesOrderNumber);
}
}
Using context As New AdventureWorksEntities
Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
Dim onlineOrders = _
From order In orders _
Where order.OnlineOrderFlag = True _
Select New With { _
.SalesOrderID = order.SalesOrderID, _
.OrderDate = order.OrderDate, _
.SalesOrderNumber = order.SalesOrderNumber _
}
For Each onlineOrder In onlineOrders
Console.WriteLine("Order ID: {0} Order date: {1:d} Order number: {2}", _
onlineOrder.SalesOrderID, _
onlineOrder.OrderDate, _
onlineOrder.SalesOrderNumber)
Next
End Using
Exemplo
O exemplo a seguir retorna os pedidos em que a quantidade do pedido é maior que 2 e menor que 6.
int orderQtyMin = 2;
int orderQtyMax = 6;
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var query =
from order in context.SalesOrderDetails
where order.OrderQty > orderQtyMin && order.OrderQty < orderQtyMax
select new
{
SalesOrderID = order.SalesOrderID,
OrderQty = order.OrderQty
};
foreach (var order in query)
{
Console.WriteLine("Order ID: {0} Order quantity: {1}",
order.SalesOrderID, order.OrderQty);
}
}
Dim orderQtyMin = 2
Dim orderQtyMax = 6
Using context As New AdventureWorksEntities
Dim orders As ObjectSet(Of SalesOrderDetail) = context.SalesOrderDetails
Dim query = _
From order In orders _
Where order.OrderQty > orderQtyMin And order.OrderQty < orderQtyMax _
Select New With { _
.SalesOrderID = order.SalesOrderID, _
.OrderQty = order.OrderQty _
}
For Each order In query
Console.WriteLine("Order ID: {0} Order quantity: {1}", _
order.SalesOrderID, order.OrderQty)
Next
End Using
Exemplo
O exemplo a seguir retorna todos os produtos de cor vermelha.
String color = "Red";
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var query =
from product in context.Products
where product.Color == color
select new
{
Name = product.Name,
ProductNumber = product.ProductNumber,
ListPrice = product.ListPrice
};
foreach (var product in query)
{
Console.WriteLine("Name: {0}", product.Name);
Console.WriteLine("Product number: {0}", product.ProductNumber);
Console.WriteLine("List price: ${0}", product.ListPrice);
Console.WriteLine("");
}
}
Dim color = "Red"
Using context As New AdventureWorksEntities
Dim products As ObjectSet(Of Product) = context.Products
Dim query = _
From product In products _
Where product.Color = color _
Select New With { _
.Name = product.Name, _
.ProductNumber = product.ProductNumber, _
.ListPrice = product.ListPrice _
}
For Each product In query
Console.WriteLine("Name: {0}", product.Name)
Console.WriteLine("Product number: {0}", product.ProductNumber)
Console.WriteLine("List price: ${0}", product.ListPrice)
Console.WriteLine("")
Next
End Using
Exemplo
O exemplo a seguir usa o Where
método para localizar pedidos que foram feitos após 1º de dezembro de 2003 e, em seguida, usa a order.SalesOrderDetail
propriedade navigation para obter os detalhes de cada pedido.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
IQueryable<SalesOrderHeader> query =
from order in context.SalesOrderHeaders
where order.OrderDate >= new DateTime(2003, 12, 1)
select order;
Console.WriteLine("Orders that were made after December 1, 2003:");
foreach (SalesOrderHeader order in query)
{
Console.WriteLine("OrderID {0} Order date: {1:d} ",
order.SalesOrderID, order.OrderDate);
foreach (SalesOrderDetail orderDetail in order.SalesOrderDetails)
{
Console.WriteLine(" Product ID: {0} Unit Price {1}",
orderDetail.ProductID, orderDetail.UnitPrice);
}
}
}
Using context As New AdventureWorksEntities
Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
Dim query = _
From order In orders _
Where order.OrderDate >= New DateTime(2003, 12, 1) _
Select order
Console.WriteLine("Orders that were made after December 1, 2003:")
For Each order In query
Console.WriteLine("OrderID {0} Order date: {1:d} ", _
order.SalesOrderID, order.OrderDate)
For Each orderDetail In order.SalesOrderDetails
Console.WriteLine(" Product ID: {0} Unit Price {1}", _
orderDetail.ProductID, orderDetail.UnitPrice)
Next
Next
End Using
Em que... Contém
Exemplo
O exemplo a seguir usa uma matriz como parte de uma Where…Contains
cláusula para localizar todos os produtos que têm um ProductModelID
que corresponde a um valor na matriz.
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
int?[] productModelIds = {19, 26, 118};
var products = from p in AWEntities.Products
where productModelIds.Contains(p.ProductModelID)
select p;
foreach (var product in products)
{
Console.WriteLine("{0}: {1}", product.ProductModelID, product.ProductID);
}
}
Using AWEntities As New AdventureWorksEntities()
Dim productModelIds As System.Nullable(Of Integer)() = {19, 26, 118}
Dim products = From p In AWEntities.Products _
Where productModelIds.Contains(p.ProductModelID) _
Select p
For Each product In products
Console.WriteLine("{0}: {1}", product.ProductModelID, product.ProductID)
Next
End Using
Nota
Como parte do predicado em uma Where…Contains
cláusula, você pode usar um Array, a List<T>ou uma coleção de qualquer tipo que implemente a IEnumerable<T> interface. Você também pode declarar e inicializar uma coleção dentro de uma consulta LINQ to Entities. Consulte o próximo exemplo para obter mais informações.
Exemplo
O exemplo a seguir declara e inicializa matrizes em uma Where…Contains
cláusula para localizar todos os produtos que têm um ProductModelID
ou Size
que correspondem a valores nas matrizes.
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
var products = from p in AWEntities.Products
where (new int?[] { 19, 26, 18 }).Contains(p.ProductModelID) ||
(new string[] { "L", "XL" }).Contains(p.Size)
select p;
foreach (var product in products)
{
Console.WriteLine("{0}: {1}, {2}", product.ProductID,
product.ProductModelID,
product.Size);
}
}
Using AWEntities As New AdventureWorksEntities()
Dim products = From p In AWEntities.Products _
Where (New System.Nullable(Of Integer)() {19, 26, 18}).Contains(p.ProductModelID) _
OrElse (New String() {"L", "XL"}).Contains(p.Size) _
Select p
For Each product In products
Console.WriteLine("{0}: {1}, {2}", product.ProductID, _
product.ProductModelID, _
product.Size)
Next
End Using