Exempel på frågeuttryckssyntax: Aggregerade operatorer (LINQ till DataSet)

Artikel
02/22/2024

Exemplen i det här avsnittet visar hur du använder Averagemetoderna , Count, Max, Minoch Sum för att köra frågor mot en DataSet och aggregera data med hjälp av frågeuttryckssyntax.

Metoden FillDataSet som används i dessa exempel anges i Läsa in data i en datauppsättning.

Exemplen i det här avsnittet använder tabellerna Kontakt, Adress, Produkt, SalesOrderHeader och SalesOrderDetail i Exempeldatabasen AdventureWorks.

Exemplen i det här avsnittet använder följande using/Imports instruktioner:

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

Mer information finns i How to: Create a LINQ to DataSet Project in Visual Studio (Så här skapar du ett LINQ till datauppsättningsprojekt i Visual Studio).

Genomsnitt

Exempel

I det Average här exemplet används metoden för att hitta det genomsnittliga listpriset för produkterna i varje formatmall.

// 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: {0} Average list price: {1}",
        product.Style, 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

Exempel

I det här exemplet används Average för att hämta den genomsnittliga totalsumman för varje kontakt-ID.

// 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 = {0} \t Average TotalDue = {1}",
        order.Category,
        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

Exempel

I det här exemplet används Average för att hämta beställningarna med medelvärdet TotalDue för varje kontakt.

// 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: {0}", orderGroup.Category);
    foreach (var order in orderGroup.CheapestProducts)
    {
        Console.WriteLine("Average total due for SalesOrderID {1} is: {0}",
            order.Field<decimal>("TotalDue"),
            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 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

Antal

Exempel

I det här exemplet används Count för att returnera en lista över kontakt-ID:t och hur många beställningar var och en har.

// 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 = {0} \t OrderCount = {1}",
        contact.CustomerID,
        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

Exempel

I det här exemplet grupperas produkter efter färg och används Count för att returnera antalet produkter i varje färggrupp.

// 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 = {0} \t ProductCount = {1}",
        product.Color,
        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

Exempel

I det Max här exemplet används metoden för att få den största summan för varje kontakt-ID.

// 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 = {0} \t Maximum TotalDue = {1}",
        order.Category, 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

Exempel

I det Max här exemplet används metoden för att hämta order med störst TotalDue för varje kontakt-ID.

// 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: {0}", orderGroup.Category);
    foreach (var order in orderGroup.CheapestProducts)
    {
        Console.WriteLine("MaxTotalDue {0} for SalesOrderID {1}: ",
            order.Field<decimal>("TotalDue"),
            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

Exempel

I det Min här exemplet används metoden för att få den minsta summa som ska betalas för varje kontakt-ID.

// 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 = {0} \t Minimum TotalDue = {1}",
        order.Category, 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

Exempel

I det Min här exemplet används metoden för att hämta beställningarna med den minsta summa som ska betalas för varje kontakt.

// 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: {0}", orderGroup.Category);
    foreach (var order in orderGroup.smallestTotalDue)
    {
        Console.WriteLine("Minimum TotalDue {0} for SalesOrderID {1}: ",
            order.Field<decimal>("TotalDue"),
            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

Sum

Exempel

I det Sum här exemplet används metoden för att hämta det totala förfallet för varje kontakt-ID.

// 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 = {0} \t TotalDue sum = {1}",
        order.Category, 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

Dela via

Exempel på frågeuttryckssyntax: Aggregerade operatorer (LINQ till DataSet)

Genomsnitt

Exempel

Exempel

Exempel

Antal

Exempel

Exempel

Max

Exempel

Exempel

Min

Exempel

Exempel

Sum

Exempel

Se även

Ytterligare resurser