Exemples de syntaxe d'expression de requête : restriction (LINQ to DataSet)

Article
04/19/2011

Les exemples de cette rubrique montrent comment utiliser la méthode Where pour interroger un DataSet à l'aide de la syntaxe d'expression de requête.

La méthode FillDataSet utilisée dans ces exemples est spécifiée dans Chargement de données dans un DataSet.

Les exemples de cette rubrique utilisent les tables Contact, Address, Product, SalesOrderHeader et SalesOrderDetail de l'exemple de base de données AdventureWorks.

Les exemples de cette rubrique utilisent les instructions using/Imports suivantes :

Option Explicit On

Imports System
Imports System.Linq
Imports System.Linq.Expressions
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Globalization

using System;
using System.Linq;
using System.Linq.Expressions;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Globalization;

Pour plus d'informations, voir Procédure : créer un projet LINQ to DataSet dans Visual Studio.

Where

Exemple

Cet exemple retourne toutes les commandes en ligne.

' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)

Dim orders As DataTable = ds.Tables("SalesOrderHeader")

Dim query = _
    From order In orders.AsEnumerable() _
    Where order.Field(Of Boolean)("OnlineOrderFlag") = True _
    Select New With { _
        .SalesOrderID = order.Field(Of Integer)("SalesOrderID"), _
        .OrderDate = order.Field(Of DateTime)("OrderDate"), _
        .SalesOrderNumber = order.Field(Of String)("SalesOrderNumber") _
     }

For Each onlineOrder In query
    Console.Write("Order ID: " & onlineOrder.SalesOrderID)
    Console.Write(" Order date: " & onlineOrder.OrderDate)
    Console.WriteLine(" Order number: " & onlineOrder.SalesOrderNumber)
Next

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable orders = ds.Tables["SalesOrderHeader"];

var query =
    from order in orders.AsEnumerable()
    where order.Field<bool>("OnlineOrderFlag") == true
    select new
    {
        SalesOrderID = order.Field<int>("SalesOrderID"),
        OrderDate = order.Field<DateTime>("OrderDate"),
        SalesOrderNumber = order.Field<string>("SalesOrderNumber")
    };

foreach (var onlineOrder in query)
{
    Console.WriteLine("Order ID: {0} Order date: {1:d} Order number: {2}",
        onlineOrder.SalesOrderID,
        onlineOrder.OrderDate,
        onlineOrder.SalesOrderNumber);
}

Exemple

Cet exemple retourne les commandes où la quantité commandée est supérieure à 2 et inférieure à 6.

' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)

Dim orders As DataTable = ds.Tables("SalesOrderDetail")

Dim query = _
    From order In orders.AsEnumerable() _
    Where order.Field(Of Short)("OrderQty") > 2 And _
            order.Field(Of Short)("OrderQty") < 6 _
    Select New With _
    { _
        .SalesOrderID = order.Field(Of Integer)("SalesOrderID"), _
        .OrderQty = order.Field(Of Short)("OrderQty") _
    }

For Each order In query
    Console.Write("Order ID: " & order.SalesOrderID)
    Console.WriteLine(" Order quantity: " & order.OrderQty)
Next

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable orders = ds.Tables["SalesOrderDetail"];

var query =
    from order in orders.AsEnumerable()
    where order.Field<Int16>("OrderQty") > 2 &&
        order.Field<Int16>("OrderQty") < 6
    select new
    {
        SalesOrderID = (int)order.Field<int>("SalesOrderID"),
        OrderQty = order.Field<Int16>("OrderQty")
    };

foreach (var order in query)
{
    Console.WriteLine("Order ID: {0} Order quantity: {1}",
        order.SalesOrderID, order.OrderQty);
}

Exemple

Cet exemple retourne tous les produits de couleur rouge.

' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)

Dim products As DataTable = ds.Tables("Product")

Dim query = _
    From product In products.AsEnumerable() _
    Where product.Field(Of String)("Color") = "Red" _
    Select New With _
       { _
           .Name = product.Field(Of String)("Name"), _
           .ProductNumber = product.Field(Of String)("ProductNumber"), _
           .ListPrice = product.Field(Of Decimal)("ListPrice") _
       }

For Each product In query
    Console.WriteLine("Name: " & product.Name)
    Console.WriteLine("Product number: " & product.ProductNumber)
    Console.WriteLine("List price: $ " & product.ListPrice & vbNewLine)
Next

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable products = ds.Tables["Product"];

var query =
    from product in products.AsEnumerable()
    where product.Field<string>("Color") == "Red"
    select new
    {
        Name = product.Field<string>("Name"),
        ProductNumber = product.Field<string>("ProductNumber"),
        ListPrice = product.Field<Decimal>("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("");
}

Exemple

Cet exemple utilise la méthode Where pour rechercher des commandes qui ont été passées après le 1er décembre 2002, puis utilise la méthode GetChildRows pour obtenir les détails de chaque commande.

' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)

Dim orders As DataTable = ds.Tables("SalesOrderHeader")

Dim query As IEnumerable(Of DataRow) = _
    From order In orders.AsEnumerable() _
    Where order.Field(Of DateTime)("OrderDate") >= New DateTime(2002, 12, 1) _
    Select order

Console.WriteLine("Orders that were made after 12/1/2002:")
For Each order As DataRow In query
    Console.WriteLine("OrderID {0} Order date: {1:d} ", _
        order.Field(Of Integer)("SalesOrderID"), order.Field(Of DateTime)("OrderDate"))
    For Each orderDetail As DataRow In order.GetChildRows("SalesOrderHeaderDetail")
        Console.WriteLine("  Product ID: {0} Unit Price {1}", _
            orderDetail("ProductID"), orderDetail("UnitPrice"))
    Next
Next

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable orders = ds.Tables["SalesOrderHeader"];

IEnumerable<DataRow> query =
    from order in orders.AsEnumerable()
    where order.Field<DateTime>("OrderDate") >= new DateTime(2002, 12, 1)
    select order;


Console.WriteLine("Orders that were made after 12/1/2002:");
foreach (DataRow order in query)
{
    Console.WriteLine("OrderID {0} Order date: {1:d} ",
        order.Field<int>("SalesOrderID"), order.Field<DateTime>("OrderDate"));
    foreach (DataRow orderDetail in order.GetChildRows("SalesOrderHeaderDetail"))
    {
        Console.WriteLine("  Product ID: {0} Unit Price {1}",
            orderDetail["ProductID"], orderDetail["UnitPrice"]);
    }
}