Partager via

Exemples de syntaxe d’expression de requête : Opérateurs d’agrégation (LINQ to DataSet)

Les exemples de cette rubrique montrent comment utiliser les méthodes Average, Count, Max, Min, et Sum pour interroger un DataSet et agréger des données à 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.

Moyen

Exemple :

Cet exemple utilise la Average méthode pour rechercher le prix de liste moyen des produits de chaque style.

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

var products = ds.Tables["Product"].AsEnumerable();

var query = from product in products
            group product by product.Field<string>("Style") into g
            select new
            {
                Style = g.Key,
                AverageListPrice =
                    g.Average(product => product.Field<Decimal>("ListPrice"))
            };

foreach (var product in query)
{
    Console.WriteLine($"Product style: {product.Style} Average list price: {product.AverageListPrice}");
}

' 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 IEnumerable(Of DataRow) = _
    ds.Tables("Product").AsEnumerable()

Dim query = _
    From product In products _
    Group product By style = product.Field(Of String)("Style") Into g = Group _
    Select New With _
        { _
            .Style = style, _
            .AverageListPrice = g.Average(Function(product) _
                    product.Field(Of Decimal)("ListPrice")) _
        }

For Each product In query
    Console.WriteLine("Product style: {0} Average list price: {1}", _
        product.Style, product.AverageListPrice)
Next

Exemple :

Cet exemple utilise Average pour obtenir le total moyen dû pour chaque ID de contact.

// 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()
    group order by order.Field<Int32>("ContactID") into g
    select new
    {
        Category = g.Key,
        averageTotalDue =
            g.Average(order => order.Field<decimal>("TotalDue"))
    };

foreach (var order in query)
{
    Console.WriteLine($"ContactID = {order.Category} \t Average TotalDue = {order.averageTotalDue}");
}

' 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() _
    Group order By contactID = order.Field(Of Int32)("ContactID") Into g = Group _
    Select New With _
    { _
        .Category = contactID, _
        .averageTotalDue = g.Average(Function(order) order. _
                Field(Of Decimal)("TotalDue")) _
    }

For Each order In query
    Console.WriteLine("ContactID = {0} " & vbTab & _
        " Average TotalDue = {1}", order.Category, _
        order.averageTotalDue)
Next

Exemple :

Cet exemple utilise Average pour obtenir les commandes en utilisant la moyenne TotalDue pour chaque contact.

// 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()
    group order by order.Field<Int32>("ContactID") into g
    let averageTotalDue = g.Average(order => order.Field<decimal>("TotalDue"))
    select new
    {
        Category = g.Key,
        CheapestProducts =
            g.Where(order => order.Field<decimal>("TotalDue") ==
                        averageTotalDue)
    };

foreach (var orderGroup in query)
{
    Console.WriteLine($"ContactID: {orderGroup.Category}");
    foreach (var order in orderGroup.CheapestProducts)
    {
        Console.WriteLine($"Average total due for SalesOrderID {order.Field<Int32>("SalesOrderID")} is: {order.Field<decimal>("TotalDue")}");
    }
    Console.WriteLine("");
}

' 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() _
    Group order By contactID = order.Field(Of Int32)("ContactID") Into g = Group _
    Let averageTotalDue = g.Average(Function(order) order.Field(Of Decimal)("TotalDue")) _
    Select New With _
    { _
        .Category = contactID, _
        .CheapestProducts = g.Where(Function(order) order. _
                Field(Of Decimal)("TotalDue") = averageTotalDue) _
    }

For Each orderGroup In query
    Console.WriteLine("ContactID: " & orderGroup.Category)
    For Each order In orderGroup.CheapestProducts
        Console.WriteLine("Average total due for SalesOrderID {1} is: {0}", _
            order.Field(Of Decimal)("TotalDue"), _
            order.Field(Of Int32)("SalesOrderID"))
    Next
    Console.WriteLine("")
Next

Nombre

Exemple :

Cet exemple utilise Count pour renvoyer une liste d'identifiants de contact et combien de commandes chacun a passées.

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

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

var query = from contact in contacts.AsEnumerable()
            select new
            {
                CustomerID = contact.Field<int>("ContactID"),
                OrderCount =
                    contact.GetChildRows("SalesOrderContact").Count()
            };

foreach (var contact in query)
{
    Console.WriteLine($"CustomerID = {contact.CustomerID} \t OrderCount = {contact.OrderCount}");
}

' 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 query = _
    From contact In contacts.AsEnumerable() _
    Select New With _
    { _
        .ContactID = contact.Field(Of Integer)("ContactID"), _
        .OrderCount = contact.GetChildRows("SalesOrderContact").Count() _
    }

For Each contact In query
    Console.Write("CustomerID = " & contact.ContactID)
    Console.WriteLine(vbTab & "OrderCount = " & contact.OrderCount)
Next

Exemple :

Cet exemple regroupe les produits par couleur et utilise Count pour retourner le nombre de produits dans chaque groupe de couleurs.

// 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()
    group product by product.Field<string>("Color") into g
    select new { Color = g.Key, ProductCount = g.Count() };

foreach (var product in query)
{
    Console.WriteLine($"Color = {product.Color} \t ProductCount = {product.ProductCount}");
}

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() _
    Group product By color = product.Field(Of String)("Color") Into g = Group _
    Select New With {.Color = color, .ProductCount = g.Count()}

For Each product In query
    Console.WriteLine("Color = {0} " & vbTab & "ProductCount = {1}", _
        product.Color, _
        product.ProductCount)
Next

Max

Exemple :

Cet exemple utilise la Max méthode pour obtenir le plus grand total dû pour chaque ID de contact.

// 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()
    group order by order.Field<Int32>("ContactID") into g
    select new
    {
        Category = g.Key,
        maxTotalDue =
            g.Max(order => order.Field<decimal>("TotalDue"))
    };

foreach (var order in query)
{
    Console.WriteLine($"ContactID = {order.Category} \t Maximum TotalDue = {order.maxTotalDue}");
}

' 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() _
   Group order By contactID = order.Field(Of Int32)("ContactID") Into g = Group _
   Select New With _
   { _
       .Category = contactID, _
       .maxTotalDue = _
           g.Max(Function(order) order.Field(Of Decimal)("TotalDue")) _
   }
For Each order In query
    Console.WriteLine("ContactID = {0} " & vbTab & _
        " Maximum TotalDue = {1}", _
        order.Category, order.maxTotalDue)
Next

Exemple :

Cet exemple utilise la méthode Max pour obtenir les commandes avec le plus grand critère TotalDue pour chaque ID de contact.

// 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()
    group order by order.Field<Int32>("ContactID") into g
    let maxTotalDue = g.Max(order => order.Field<decimal>("TotalDue"))
    select new
    {
        Category = g.Key,
        CheapestProducts =
            g.Where(order => order.Field<decimal>("TotalDue") ==
                        maxTotalDue)
    };

foreach (var orderGroup in query)
{
    Console.WriteLine($"ContactID: {orderGroup.Category}");
    foreach (var order in orderGroup.CheapestProducts)
    {
        Console.WriteLine($"MaxTotalDue {order.Field<decimal>("TotalDue")} for SalesOrderID {order.Field<Int32>("SalesOrderID")}: ");
    }
}

' 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() _
    Group order By contactID = order.Field(Of Int32)("ContactID") Into g = Group _
    Let maxTotalDue = g.Max(Function(order) order.Field(Of Decimal)("TotalDue")) _
    Select New With _
    { _
        .Category = contactID, _
        .CheapestProducts = _
            g.Where(Function(order) order. _
                    Field(Of Decimal)("TotalDue") = maxTotalDue) _
    }

For Each orderGroup In query
    Console.WriteLine("ContactID: " & orderGroup.Category)
    For Each order In orderGroup.CheapestProducts
        Console.WriteLine("MaxTotalDue {0} for SalesOrderID {1} ", _
            order.Field(Of Decimal)("TotalDue"), _
            order.Field(Of Int32)("SalesOrderID"))
    Next
Next

Min

Exemple :

Cet exemple utilise la Min méthode pour obtenir le plus petit total dû pour chaque ID de contact.

// 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()
    group order by order.Field<Int32>("ContactID") into g
    select new
    {
        Category = g.Key,
        smallestTotalDue =
            g.Min(order => order.Field<decimal>("TotalDue"))
    };

foreach (var order in query)
{
    Console.WriteLine($"ContactID = {order.Category} \t Minimum TotalDue = {order.smallestTotalDue}");
}

' 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() _
    Group order By contactID = order.Field(Of Int32)("ContactID") Into g = Group _
    Select New With _
    { _
       .Category = contactID, _
       .smallestTotalDue = g.Min(Function(order) _
                order.Field(Of Decimal)("TotalDue")) _
    }

For Each order In query
    Console.WriteLine("ContactID = {0} " & vbTab & _
        "Minimum TotalDue = {1}", order.Category, order.smallestTotalDue)
Next

Exemple :

Cet exemple utilise la méthode Min pour obtenir les commandes présentant le montant total dû le plus bas pour chaque ID de contact.

// 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()
    group order by order.Field<Int32>("ContactID") into g
    let minTotalDue = g.Min(order => order.Field<decimal>("TotalDue"))
    select new
    {
        Category = g.Key,
        smallestTotalDue =
            g.Where(order => order.Field<decimal>("TotalDue") ==
                        minTotalDue)
    };

foreach (var orderGroup in query)
{
    Console.WriteLine($"ContactID: {orderGroup.Category}");
    foreach (var order in orderGroup.smallestTotalDue)
    {
        Console.WriteLine($"Minimum TotalDue {order.Field<decimal>("TotalDue")} for SalesOrderID {order.Field<Int32>("SalesOrderID")}: ");
    }
    Console.WriteLine("");
}

' 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() _
    Group order By contactID = order.Field(Of Int32)("ContactID") Into g = Group _
    Let minTotalDue = g.Min(Function(order) order.Field(Of Decimal)("TotalDue")) _
    Select New With _
    { _
       .Category = contactID, _
       .smallestTotalDue = g.Where(Function(order) order. _
            Field(Of Decimal)("TotalDue") = minTotalDue) _
    }

For Each orderGroup In query
    Console.WriteLine("ContactID: " & orderGroup.Category)
    For Each order In orderGroup.smallestTotalDue
        Console.WriteLine("Minimum TotalDue {0} for SalesOrderID {1} ", _
            order.Field(Of Decimal)("TotalDue"), _
            order.Field(Of Int32)("SalesOrderID"))
    Next
    Console.WriteLine("")
Next

Somme

Exemple :

Cet exemple utilise la Sum méthode pour obtenir le total dû pour chaque ID de contact.

// 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()
    group order by order.Field<Int32>("ContactID") into g
    select new
    {
        Category = g.Key,
        TotalDue = g.Sum(order => order.Field<decimal>("TotalDue")),
    };
foreach (var order in query)
{
    Console.WriteLine($"ContactID = {order.Category} \t TotalDue sum = {order.TotalDue}");
}

' 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() _
    Group order By contactID = order.Field(Of Int32)("ContactID") Into g = Group _
    Select New With _
       { _
           .Category = contactID, _
           .TotalDue = g.Sum(Function(order) order. _
                Field(Of Decimal)("TotalDue")) _
       }

For Each order In query
    Console.WriteLine("ContactID = {0} " & vbTab & "TotalDue sum = {1}", _
        order.Category, order.TotalDue)
Next

Voir aussi

  • Last updated on