Beispiele für die Abfrageausdruckssyntax: Aggregatoperatoren (LINQ to Entities)

In den Beispielen in diesem Thema wird gezeigt, wie Sie die Methoden Average, Count, Max, Min und Sum verwenden können, um das AdventureWorks Sales-Modell unter Verwendung der Abfrageausdruckssyntax abzufragen. Für das in den Beispielen verwendete AdventureWorks Sales-Modell wurde auf die Contact-, Address-, Product-, SalesOrderHeader- und SalesOrderDetail-Tabellen der AdventureWorks-Beispieldatenbank zurückgegriffen.

In den Beispielen in diesem Thema werden die folgenden using/Imports-Anweisungen verwendet:

Option Explicit On
Option Strict On
Imports System.Data.Objects
Imports System.Globalization

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;

Mittelwert

Beispiel

Im folgenden Beispiel wird die Average-Methode verwendet, um den durchschnittlichen Listenpreis der Produkte jeder Produktart zu ermitteln.

Using context As New AdventureWorksEntities
    Dim products As ObjectSet(Of Product) = context.Products

    Dim query = _
        From prod In products _
        Let styl = prod.Style _
        Group prod By styl Into g = Group _
        Select New With _
        { _
            .Style = styl, _
            .AverageListPrice = g.Average(Function(p) p.ListPrice) _
        }

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

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<Product> products = context.Products;

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

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

Beispiel

Im folgenden Beispiel wird Average verwendet, um für jede Kontakt-ID den durchschnittlichen fälligen Gesamtbetrag abzurufen.

Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = _
        From ord In orders _
        Let contID = ord.Contact.ContactID _
        Group ord By contID Into g = Group _
        Select New With _
        { _
            .Category = contID, _
            .averageTotalDue = _
                g.Average(Function(ord) ord.TotalDue) _
         }

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

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
        from order in orders
        group order by order.Contact.ContactID into g
        select new
        {
            Category = g.Key,
            averageTotalDue = g.Average(order => order.TotalDue)
        };

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

Beispiel

Im folgenden Beispiel wird Average verwendet, um für jeden Kontakt die Aufträge mit dem durchschnittlichen fälligen Gesamtbetrag abzurufen.

Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = _
        From ord In orders _
        Let contID = ord.Contact.ContactID _
        Group ord By contID Into g = Group _
        Let averageTotalDue = g.Average(Function(ord) ord.TotalDue) _
        Select New With _
        { _
            .Category = contID, _
            .CheapestProducts = _
                g.Where(Function(ord) ord.TotalDue = averageTotalDue) _
         }

    For Each orderGroup In query
        Console.WriteLine("ContactID: {0}", orderGroup.Category)
        For Each ord In orderGroup.CheapestProducts
            Console.WriteLine("Average total due for SalesOrderID {1} is: {0}", _
                ord.TotalDue, ord.SalesOrderID)
        Next
        Console.Write(vbNewLine)
    Next
End Using

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
        from order in orders
        group order by order.Contact.ContactID into g
        let averageTotalDue = g.Average(order => order.TotalDue)
        select new
        {
            Category = g.Key,
            CheapestProducts =
                g.Where(order => order.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.TotalDue, order.SalesOrderID);
        }
        Console.Write("\n");
    }
}

Count

Beispiel

Im folgenden Beispiel wird Count verwendet, um eine Liste der Kontakt-IDs und der jeweiligen Anzahl von Aufträgen zurückzugeben.

Using context As New AdventureWorksEntities
    Dim contacts As ObjectSet(Of Contact) = context.Contacts

    Dim query = _
        From cont In contacts _
        Select New With _
        { _
            .CustomerID = cont.ContactID, _
            .OrderCount = cont.SalesOrderHeaders.Count() _
         }

    For Each cont In query
        Console.WriteLine("CustomerID = {0}   OrderCount = {1}", _
               cont.CustomerID, cont.OrderCount)
    Next
End Using

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<Contact> contacts = context.Contacts;

    //Can't find field SalesOrderContact
    var query =
        from contact in contacts
        select new
        {
            CustomerID = contact.ContactID,
            OrderCount = contact.SalesOrderHeaders.Count()
        };

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

Beispiel

Im folgenden Beispiel werden die Produkte nach ihrer Farbe gruppiert. Anschließend wird Count verwendet, um die Anzahl der Produkte in jeder Farbgruppe zurückzugeben.

Using context As New AdventureWorksEntities
    Dim products As ObjectSet(Of Product) = context.Products

    Dim query = _
        From prod In products _
        Let pc = prod.Color _
        Group prod By pc Into g = Group _
        Select New With {.Color = pc, .ProductCount = g.Count()}

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

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<Product> products = context.Products;

    var query =
        from product in products
        group product by product.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);
    }
}

Max

Beispiel

Im folgenden Beispiel wird die Max-Methode verwendet, um für jede Kontakt-ID den größten fälligen Gesamtbetrag abzurufen.

Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = _
        From ord In orders _
        Let contID = ord.Contact.ContactID _
        Group ord By contID Into g = Group _
        Select New With _
        { _
            .Category = contID, _
            .MaxTotalDue = _
                g.Max(Function(ord) ord.TotalDue) _
         }

    For Each ord In query
        Console.WriteLine("ContactID = {0} " & vbTab & _
            " Maximum TotalDue = {1}", _
            ord.Category, ord.MaxTotalDue)
    Next
End Using

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
        from order in orders
        group order by order.Contact.ContactID into g
        select new
        {
            Category = g.Key,
            maxTotalDue =
                g.Max(order => order.TotalDue)
        };

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

Beispiel

Im folgenden Beispiel wird die Max-Methode verwendet, um für jede Kontakt-ID die Aufträge mit dem größten fälligen Gesamtbetrag abzurufen.

Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = _
        From ord In orders _
        Let contID = ord.Contact.ContactID _
        Group ord By contID Into g = Group _
        Let maxTotalDue = g.Max(Function(ord) ord.TotalDue) _
        Select New With _
        { _
            .Category = contID, _
            .CheapestProducts = _
                g.Where(Function(ord) ord.TotalDue = maxTotalDue) _
         }

    For Each orderGroup In query
        Console.WriteLine("ContactID: {0}", orderGroup.Category)
        For Each ord In orderGroup.CheapestProducts
            Console.WriteLine("MaxTotalDue {0} for SalesOrderID {1}: ", _
                ord.TotalDue, ord.SalesOrderID)
        Next
        Console.Write(vbNewLine)
    Next
End Using

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
        from order in orders
        group order by order.Contact.ContactID into g
        let maxTotalDue = g.Max(order => order.TotalDue)
        select new
        {
            Category = g.Key,
            CheapestProducts =
                g.Where(order => order.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.TotalDue,
                order.SalesOrderID);
        }
        Console.Write("\n");
    }
}

Min

Beispiel

Im folgenden Beispiel wird die Min-Methode verwendet, um für jede Kontakt-ID den kleinsten fälligen Gesamtbetrag abzurufen.

Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = _
        From ord In orders _
        Let contID = ord.Contact.ContactID _
        Group ord By contID Into g = Group _
        Select New With _
        { _
            .Category = contID, _
            .smallestTotalDue = _
                g.Min(Function(o) o.TotalDue) _
         }

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

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
        from order in orders
        group order by order.Contact.ContactID into g
        select new
        {
            Category = g.Key,
            smallestTotalDue =
                g.Min(order => order.TotalDue)
        };

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

Beispiel

Im folgenden Beispiel wird die Min-Methode verwendet, um für jeden Kontakt die Aufträge mit dem kleinsten fälligen Gesamtbetrag abzurufen.

Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = _
        From ord In orders _
        Let contID = ord.Contact.ContactID _
        Group ord By contID Into g = Group _
        Let minTotalDue = g.Min(Function(o) o.TotalDue) _
        Select New With _
        { _
            .Category = contID, _
            .smallestTotalDue = _
                g.Where(Function(o) o.TotalDue = minTotalDue) _
         }

    For Each orderGroup In query
        Console.WriteLine("ContactID: {0}", orderGroup.Category)
        For Each ord In orderGroup.smallestTotalDue
            Console.WriteLine("Mininum TotalDue {0} for SalesOrderID {1}: ", _
                ord.TotalDue, ord.SalesOrderID)
        Next
        Console.Write(vbNewLine)
    Next
End Using

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
        from order in orders
        group order by order.Contact.ContactID into g
        let minTotalDue = g.Min(order => order.TotalDue)
        select new
        {
            Category = g.Key,
            smallestTotalDue =
                g.Where(order => order.TotalDue == minTotalDue)
        };


    foreach (var orderGroup in query)
    {
        Console.WriteLine("ContactID: {0}", orderGroup.Category);
        foreach (var order in orderGroup.smallestTotalDue)
        {
            Console.WriteLine("Mininum TotalDue {0} for SalesOrderID {1}: ",
                order.TotalDue,
                order.SalesOrderID);
        }
        Console.Write("\n");
    }
}

Sum

Beispiel

Im folgenden Beispiel wird die Sum-Methode verwendet, um für jede Kontakt-ID den fälligen Gesamtbetrag abzurufen.

Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = _
        From ord In orders _
        Let contID = ord.Contact.ContactID _
        Group ord By contID Into g = Group _
        Select New With _
        { _
            .Category = contID, _
            .TotalDue = g.Sum(Function(o) o.TotalDue) _
        }

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

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
        from order in orders
        group order by order.Contact.ContactID into g
        select new
        {
            Category = g.Key,
            TotalDue = g.Sum(order => order.TotalDue)
        };

    foreach (var order in query)
    {
        Console.WriteLine("ContactID = {0} \t TotalDue sum = {1}",
            order.Category, order.TotalDue);
    }
}

Siehe auch

Konzepte

Abfragen in LINQ to Entities