Partager via

Exemples de syntaxe d’expression de requête : Projection (LINQ to DataSet)

Les exemples de cette rubrique montrent comment utiliser les méthodes Select et SelectMany pour interroger un DataSet à l’aide de la syntaxe d’expression de requête.

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

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

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

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Windows.Forms;

Option Explicit On

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

Pour plus d’informations, consultez Guide pratique pour créer un projet LINQ to DataSet dans Visual Studio.

Sélectionnez

Exemple :

Cet exemple utilise la Select méthode pour renvoyer toutes les lignes de la Product table et afficher les noms de produits.

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

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

IEnumerable<DataRow> query =
    from product in products.AsEnumerable()
    select product;

Console.WriteLine("Product Names:");
foreach (DataRow p in query)
{
    Console.WriteLine(p.Field<string>("Name"));
}

' 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() _
            Select product
Console.WriteLine("Product Names:")
For Each p In query
    Console.WriteLine(p.Field(Of String)("Name"))
Next

Exemple :

Cet exemple utilise Select pour renvoyer une séquence de noms de produits uniquement.

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

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

IEnumerable<string> query =
    from product in products.AsEnumerable()
    select product.Field<string>("Name");

Console.WriteLine("Product Names:");
foreach (string productName in query)
{
    Console.WriteLine(productName);
}

' 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() _
            Select product.Field(Of String)("Name")

Console.WriteLine("Product Names:")
For Each productName In query
    Console.WriteLine(productName)
Next

SelectMany

Exemple :

Cet exemple utilise From …, … (l’équivalent de la SelectMany méthode) pour sélectionner toutes les commandes dont TotalDue la valeur est inférieure à 500.00.

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

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

var query =
    from contact in contacts.AsEnumerable()
    from order in orders.AsEnumerable()
    where contact.Field<int>("ContactID") == order.Field<int>("ContactID")
        && order.Field<decimal>("TotalDue") < 500.00M
    select new
    {
        ContactID = contact.Field<int>("ContactID"),
        LastName = contact.Field<string>("LastName"),
        FirstName = contact.Field<string>("FirstName"),
        OrderID = order.Field<int>("SalesOrderID"),
        Total = order.Field<decimal>("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);
}

' 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 contacts As DataTable = ds.Tables("Contact")
Dim orders As DataTable = ds.Tables("SalesOrderHeader")

Dim query = _
    From contact In contacts.AsEnumerable() _
    From order In orders.AsEnumerable() _
    Where (contact.Field(Of Integer)("ContactID") = _
        order.Field(Of Integer)("ContactID")) _
        And (order.Field(Of Decimal)("TotalDue") < 500D) _
    Select New With _
    { _
        .ContactID = contact.Field(Of Integer)("ContactID"), _
        .LastName = contact.Field(Of String)("LastName"), _
        .FirstName = contact.Field(Of String)("FirstName"), _
        .OrderID = order.Field(Of Integer)("SalesOrderID"), _
        .TotalDue = order.Field(Of Decimal)("TotalDue") _
    }

For Each smallOrder In query
    Console.Write("ContactID: " & smallOrder.ContactID)
    Console.Write(" Name: {0}, {1}", smallOrder.LastName, _
                       smallOrder.FirstName)
    Console.Write(" OrderID: " & smallOrder.OrderID)
    Console.WriteLine(" TotalDue: $" & smallOrder.TotalDue)
Next

Exemple :

Cet exemple utilise From …, … (l’équivalent de la SelectMany méthode) pour sélectionner toutes les commandes où la commande a été effectuée le 1er octobre 2002 ou version ultérieure.

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

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

var query =
    from contact in contacts.AsEnumerable()
    from order in orders.AsEnumerable()
    where contact.Field<int>("ContactID") == order.Field<int>("ContactID") &&
        order.Field<DateTime>("OrderDate") >= new DateTime(2002, 10, 1)
    select new
    {
        ContactID = contact.Field<int>("ContactID"),
        LastName = contact.Field<string>("LastName"),
        FirstName = contact.Field<string>("FirstName"),
        OrderID = order.Field<int>("SalesOrderID"),
        OrderDate = order.Field<DateTime>("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);
}

' 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 contacts As DataTable = ds.Tables("Contact")
Dim orders As DataTable = ds.Tables("SalesOrderHeader")

Dim query = _
    From contact In contacts.AsEnumerable() _
    From order In orders.AsEnumerable() _
    Where contact.Field(Of Integer)("ContactID") = order.Field(Of Integer)("ContactID") And _
        order.Field(Of DateTime)("OrderDate") >= New DateTime(2002, 10, 1) _
    Select New With _
        { _
           .ContactID = contact.Field(Of Integer)("ContactID"), _
           .LastName = contact.Field(Of String)("LastName"), _
           .FirstName = contact.Field(Of String)("FirstName"), _
           .OrderID = order.Field(Of Integer)("SalesOrderID"), _
           .OrderDate = order.Field(Of DateTime)("OrderDate") _
        }

For Each order In query
    Console.Write("Contact ID: " & order.ContactID)
    Console.Write(" Name: " & order.LastName & ", " & order.FirstName)
    Console.Write(" Order ID: " & order.OrderID)
    Console.WriteLine(" Order date: {0:d} ", order.OrderDate)
Next

Exemple :

Cet exemple utilise un From …, … (équivalent de la méthode SelectMany) pour sélectionner toutes les commandes où le total de la commande est supérieur à 10000,00 et utilise l’affectation From pour éviter de calculer le total deux fois.

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

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

var query =
    from contact in contacts.AsEnumerable()
    from order in orders.AsEnumerable()
    let total = order.Field<decimal>("TotalDue")
    where contact.Field<int>("ContactID") == order.Field<int>("ContactID") &&
          total >= 10000.0M
    select new
    {
        ContactID = contact.Field<int>("ContactID"),
        LastName = contact.Field<string>("LastName"),
        OrderID = order.Field<int>("SalesOrderID"),
        total
    };
foreach (var order in query)
{
    Console.WriteLine($"Contact ID: {order.ContactID} Last name: {order.LastName} Order ID: {order.OrderID} Total: {order.total}");
}


' 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 contacts As DataTable = ds.Tables("Contact")
Dim orders As DataTable = ds.Tables("SalesOrderHeader")

Dim query = _
    From contact In contacts.AsEnumerable() _
    From order In orders.AsEnumerable() _
    Let total = order.Field(Of Decimal)("TotalDue") _
    Where contact.Field(Of Integer)("ContactID") = order.Field(Of Integer)("ContactID") And _
        total >= 10000D _
    Select New With _
        { _
           .ContactID = contact.Field(Of Integer)("ContactID"), _
           .LastName = contact.Field(Of String)("LastName"), _
           .OrderID = order.Field(Of Integer)("SalesOrderID"), _
           .OrderDate = order.Field(Of DateTime)("OrderDate"), _
           total _
        }

For Each order In query
    Console.Write("Contact ID: " & order.ContactID)
    Console.Write(" Last Name: " & order.LastName)
    Console.Write(" Order ID: " & order.OrderID)
    Console.WriteLine(" Total: $" & order.total)
Next

Voir aussi

  • Last updated on