Esempi di sintassi di query basate sul metodo: filtroMethod-Based Query Syntax Examples: Filtering

Negli esempi in questo argomento viene illustrato come utilizzare il Where e Where…Contains metodi per eseguire una query di modello Sales di AdventureWorks utilizzando la sintassi di query basate su metodo.The examples in this topic demonstrate how to use the Where and Where…Contains methods to query the AdventureWorks Sales Model using method-based query syntax. Si noti che in...ContainsNote, Where…Contains non può essere utilizzato come parte di un query compilata.cannot be used as a part of a compiled query.

Il modello Sales di AdventureWorks usato in questi esempi è compilato in base alle tabelle Contact, Address, Product, SalesOrderHeader e SalesOrderDetail del database di esempio AdventureWorks.The AdventureWorks Sales model used in these examples is built from the Contact, Address, Product, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorks sample database.

Gli esempi in questo argomento usano seguenti using / Imports istruzioni:The examples in this topic use the following using/Imports statements:

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

DoveWhere

EsempioExample

Nell'esempio seguente vengono restituiti tutti gli ordini online.The following example returns all online orders.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var onlineOrders = context.SalesOrderHeaders
        .Where(order => order.OnlineOrderFlag == true)
        .Select(s => new { s.SalesOrderID, s.OrderDate, s.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 onlineOrders = context.SalesOrderHeaders _
        .Where(Function(order) order.OnlineOrderFlag = True) _
        .Select(Function(order) 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

EsempioExample

Nell'esempio seguente vengono restituiti gli ordini in cui la quantità ordinata è maggiore di 2 e minore di 6.The following example returns the orders where the order quantity is greater than 2 and less than 6.

int orderQtyMin = 2;
int orderQtyMax = 6;
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var query = context.SalesOrderDetails
        .Where(order => order.OrderQty > orderQtyMin && order.OrderQty < orderQtyMax)
        .Select(s => new { s.SalesOrderID, s.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 query = context.SalesOrderDetails _
        .Where(Function(order) order.OrderQty > orderQtyMin And order.OrderQty < orderQtyMax) _
        .Select(Function(order) 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

EsempioExample

Nell'esempio seguente vengono restituiti tutti i prodotti di colore rosso.The following example returns all red colored products.

String color = "Red";
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var query = context.Products
        .Where(product => product.Color == color)
        .Select(p => new { p.Name, p.ProductNumber, p.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 query = context.Products _
        .Where(Function(product) product.Color = color) _
        .Select(Function(product) 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

EsempioExample

Nell'esempio seguente viene usato il metodo Where per individuare gli ordini effettuati dopo l'1 dicembre 2003, quindi viene usata la proprietà di navigazione order.SalesOrderDetail per ottenere i dettagli relativi a ogni ordine.The following example uses the Where method to find orders that were made after December 1, 2003 and then uses the order.SalesOrderDetail navigation property to get the details for each order.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    IQueryable<SalesOrderHeader> query = context.SalesOrderHeaders
        .Where(order => order.OrderDate >= new DateTime(2003, 12, 1));

    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 query = context.SalesOrderHeaders _
        .Where(Function(order) order.OrderDate >= New DateTime(2003, 12, 1)) _
        .Select(Function(order) 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

Where…ContainsWhere…Contains

EsempioExample

Nell'esempio seguente viene usata una matrice come parte di una clausola Where…Contains per trovare tutti i prodotti con un ProductModelID che corrisponde a un valore nella matrice.The following example uses an array as part of a Where…Contains clause to find all products that have a ProductModelID that matches a value in the array.

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    int?[] productModelIds = { 19, 26, 118 };
    var products = AWEntities.Products.
        Where(p => productModelIds.Contains(p.ProductModelID));
   
    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 = AWEntities.Products. _
        Where(Function(p) productModelIds.Contains(p.ProductModelID))
    For Each product In products
        Console.WriteLine("{0}: {1}", product.ProductModelID, product.ProductID)
    Next
End Using

Nota

Come parte del predicato in una clausola Where…Contains, è possibile usare un oggetto Array, un oggetto List<T>o una raccolta di qualsiasi tipo che implementa l'interfaccia IEnumerable<T>.As part of the predicate in a Where…Contains clause, you can use an Array, a List<T>, or a collection of any type that implements the IEnumerable<T> interface. È inoltre possibile dichiarare e inizializzare una raccolta all'interno di una query LINQ to Entities.You can also declare and initialize a collection within a LINQ to Entities query. Per altre informazioni, vedere l'esempio successivo.See the next example for more information.

EsempioExample

Nell'esempio seguente vengono dichiarate e inizializzate matrici in una clausola Where…Contains per trovare tutti i prodotti che dispongono di un ProductModelID o una Size che corrisponde a un valore nelle matrici.The following example declares and initializes arrays in a Where…Contains clause to find all products that have a ProductModelID or a Size that matches a value in the arrays.

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    var products = AWEntities.Products.
        Where(p => (new int?[] { 19, 26, 18 }).Contains(p.ProductModelID) || 
                   (new string[] { "L", "XL" }).Contains(p.Size));

    foreach (var product in products)
    {
        Console.WriteLine("{0}: {1}, {2}", product.ProductID, 
                                           product.ProductModelID, 
                                           product.Size);
    }
}
Using AWEntities As New AdventureWorksEntities()
    Dim products = AWEntities.Products. _
        Where(Function(p) (New System.Nullable(Of Integer)() {19, 26, 18}). _
                           Contains(p.ProductModelID) _
              OrElse _
                          (New String() {"L", "XL"}).Contains(p.Size))

    For Each product In products
        Console.WriteLine("{0}: {1}, {2}", product.ProductID, _
                                           product.ProductModelID, _
                                           product.Size)
    Next
End Using

Vedere ancheSee Also

Query in LINQ to EntitiesQueries in LINQ to Entities