Cómo filtrar datos (Entity Framework)
En este tema se describe cómo filtrar los resultados de una consulta. En el ejemplo se devuelve una colección de objetos SalesOrderHeader que representa a todos los pedidos en línea superiores a 2.500 $. Se muestra el mismo ejemplo con las siguientes tecnologías de consulta de Entity Framework:
LINQ to Entities
Entity SQL con ObjectQuery<T>
Métodos del generador de consultas de ObjectQuery<T>
Los ejemplos de este tema se basan en el modelo AdventureWorks Sales. Para ejecutar el código de este ejemplo, debe haber agregado el modelo AdventureWorks Sales al proyecto y haber configurado el proyecto para usar Entity Framework. Para ello, complete los procedimientos de Cómo configurar manualmente un proyecto de Entity Framework y Cómo definir manualmente un modelo Entity Data Model (Entity Framework). También puede utilizar el Asistente para Entity Data Model con el fin de definir el modelo AdventureWorks Sales. Para obtener más información, vea Cómo usar el Asistente para Entity Data Model (Entity Framework).
Ejemplo
A continuación se muestra el ejemplo de LINQ to Entities.
Using context As AdventureWorksEntities = _
New AdventureWorksEntities()
' Specify the order amount.
Dim orderCost As Integer = 2500
Try
' Define a LINQ query that returns only online orders
' more than the specified amount.
Dim onlineOrders = _
From order As SalesOrderHeader In context.SalesOrderHeader() _
Where order.OnlineOrderFlag = True And _
order.TotalDue > orderCost _
Select order
' Print order information.
For Each onlineOrder In onlineOrders
Console.WriteLine("Order ID:{0}Order date: " _
+ "{1}:d Order number: {2}", _
onlineOrder.SalesOrderID, _
onlineOrder.OrderDate, _
onlineOrder.SalesOrderNumber)
Next
Catch ex As EntitySqlException
Console.WriteLine(ex.ToString())
End Try
End Using
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
// Specify the order amount.
int orderCost = 2500;
try
{
// Define a LINQ query that returns only online orders
// more than the specified amount.
var onlineOrders =
from order in context.SalesOrderHeader
where order.OnlineOrderFlag == true &&
order.TotalDue > orderCost
select order;
// Print order information.
foreach (var onlineOrder in onlineOrders)
{
Console.WriteLine("Order ID: {0} Order date: "
+ "{1:d} Order number: {2}",
onlineOrder.SalesOrderID,
onlineOrder.OrderDate,
onlineOrder.SalesOrderNumber);
}
}
catch (EntitySqlException ex)
{
Console.WriteLine(ex.ToString());
}
}
A continuación se muestra el ejemplo de Entity SQL.
Using context As AdventureWorksEntities = _
New AdventureWorksEntities()
' Specify the order amount.
Dim orderCost As Integer = 2500
' Specify the Entity SQL query that returns only online orders
' more than the specified amount.
Dim queryString As String = "SELECT VALUE o FROM SalesOrderHeader AS o " _
+ "WHERE o.OnlineOrderFlag = TRUE AND o.TotalDue > @ordercost"
Try
' Define an ObjectQuery and pass the maxOrderCost parameter.
Dim onlineOrders As ObjectQuery(Of SalesOrderHeader) = _
New ObjectQuery(Of SalesOrderHeader)(queryString, context)
onlineOrders.Parameters.Add( _
New ObjectParameter("ordercost", orderCost))
' Print order information.
For Each onlineOrder In onlineOrders
Console.WriteLine("Order ID:{0}Order date: " _
+ "{1}:d Order number: {2}", _
onlineOrder.SalesOrderID, _
onlineOrder.OrderDate, _
onlineOrder.SalesOrderNumber)
Next
Catch ex As EntitySqlException
Console.WriteLine(ex.ToString())
End Try
End Using
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
// Specify the order amount.
decimal orderCost = 2500;
// Specify the Entity SQL query that returns only online orders
// more than the specified amount.
string queryString = @"SELECT VALUE o FROM SalesOrderHeader AS o
WHERE o.OnlineOrderFlag = TRUE AND o.TotalDue > @ordercost";
try
{
// Define an ObjectQuery and pass the maxOrderCost parameter.
ObjectQuery<SalesOrderHeader> onlineOrders =
new ObjectQuery<SalesOrderHeader>(queryString, context);
onlineOrders.Parameters.Add(
new ObjectParameter("ordercost", orderCost));
// Print order information.
foreach (var onlineOrder in onlineOrders)
{
Console.WriteLine("Order ID: {0} Order date: "
+ "{1:d} Order number: {2}",
onlineOrder.SalesOrderID,
onlineOrder.OrderDate,
onlineOrder.SalesOrderNumber);
}
}
catch (EntitySqlException ex)
{
Console.WriteLine(ex.ToString());
}
}
A continuación se muestra el ejemplo del método del generador de consultas.
Using context As AdventureWorksEntities = _
New AdventureWorksEntities()
' Specify the order amount.
Dim orderCost As Integer = 2500
Try
' Define an ObjectQuery that returns only online orders
' more than the specified amount.
Dim onlineOrders As ObjectQuery(Of SalesOrderHeader) = _
context.SalesOrderHeader.Where("it.OnlineOrderFlag = TRUE") _
.Where("it.TotalDue > @ordercost", _
New ObjectParameter("ordercost", orderCost))
' Print order information.
For Each onlineOrder In onlineOrders
Console.WriteLine("Order ID:{0}Order date: " _
+ "{1}:d Order number: {2}", _
onlineOrder.SalesOrderID, _
onlineOrder.OrderDate, _
onlineOrder.SalesOrderNumber)
Next
Catch ex As EntitySqlException
Console.WriteLine(ex.ToString())
End Try
End Using
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
// Specify the order amount.
int orderCost = 2500;
try
{
// Define an ObjectQuery that returns only online orders
// more than the specified amount.
ObjectQuery<SalesOrderHeader> onlineOrders =
context.SalesOrderHeader
.Where("it.OnlineOrderFlag = TRUE AND it.TotalDue > @ordercost",
new ObjectParameter("ordercost", orderCost));
// Print order information.
foreach (var onlineOrder in onlineOrders)
{
var items = (from item in context.SalesOrderDetail
where item.SalesOrderHeader == onlineOrder
select item).Take(3);
Console.WriteLine("Order ID: {0} Order date: "
+ "{1:d} Order number: {2}",
onlineOrder.SalesOrderID,
onlineOrder.OrderDate,
onlineOrder.SalesOrderNumber);
}
}
catch (EntitySqlException ex)
{
Console.WriteLine(ex.ToString());
}
}