Beispiele für die methodenbasierte Abfragesyntax: Verknüpfungsoperatoren (LINQ to Entities)

In diesem Beispiel wird gezeigt, wie Sie mithilfe der Methoden Join und GroupJoin unter Verwendung der methodenbasierten Abfragesyntax das AdventureWorks Sales-Modell abfragen. Für das in den Beispielen verwendete "AdventureWorks Sales"-Modell wurde auf die Tabellen Contact, Address, Product, SalesOrderHeader und SalesOrderDetail der "AdventureWorks"-Beispieldatenbank zurückgegriffen.

Die Beispiele in diesem Thema verwenden die folgenden using/Imports-Anweisungen:

Option Explicit On
Option Strict On
Imports L2EExamplesVB.AdventureWorksModel
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 AdventureWorksModel;
using System.Globalization;

Weitere Informationen finden Sie unter Gewusst wie: Erstellen eines LINQ to Entities-Projekts in Visual Studio.

GroupJoin

Beispiel

Im folgenden Beispiel wird eine GroupJoin zwischen der SalesOrderHeader-Tabelle und der SalesOrderDetail-Tabelle durchgeführt, um nach der Anzahl der Aufträge pro Kunde zu suchen. Eine Gruppenverknüpfung ist das Äquivalent zu einer linken äußeren Verknüpfung, die jedes Element der ersten (linken) Datenquelle zurückgibt, selbst wenn die andere Datenquelle keine zugehörigen Elemente enthält.

Using AWEntities As New AdventureWorksEntities
    Dim orders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader
    Dim details As ObjectQuery(Of SalesOrderDetail) = AWEntities.SalesOrderDetail

    Dim query = orders.GroupJoin(details, _
            Function(order) order.SalesOrderID, _
            Function(detail) detail.SalesOrderID, _
            Function(order, orderGroup) New With _
            { _
                .CustomerID = order.SalesOrderID, _
                .OrderCount = orderGroup.Count() _
            })

    For Each order In query
        Console.WriteLine("CustomerID: {0}  Orders Count: {1}", _
            order.CustomerID, order.OrderCount)
    Next

End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<SalesOrderHeader> orders = AWEntities.SalesOrderHeader;
    ObjectQuery<SalesOrderDetail> details = AWEntities.SalesOrderDetail;

    var query = orders.GroupJoin(details, 
        order => order.SalesOrderID,
        detail => detail.SalesOrderID,
        (order, orderGroup) => new
        {
            CustomerID = order.SalesOrderID,
            OrderCount = orderGroup.Count()
        });

    foreach (var order in query)
    {
        Console.WriteLine("CustomerID: {0}  Orders Count: {1}",
            order.CustomerID,
            order.OrderCount);
    }

}

Beispiel

Im folgenden Beispiel wird eine GroupJoin zwischen der Contact-Tabelle und der SalesOrderHeader-Tabelle durchgeführt, um nach der Anzahl der Aufträge pro Kontakt zu suchen. Die Auftragsanzahl und die ID für jeden Kontakt werden angezeigt.

Using AWEntities As New AdventureWorksEntities
    Dim contacts As ObjectQuery(Of Contact) = AWEntities.Contact
    Dim orders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader

    Dim query = contacts.GroupJoin(orders, _
            Function(contact) contact.ContactID, _
            Function(order) order.Contact.ContactID, _
            Function(contact, contactGroup) New With _
            { _
                .ContactID = contact.ContactID, _
                .OrderCount = contactGroup.Count(), _
                .orders = contactGroup.Select(Function(order) order) _
            })

    For Each group In query
        Console.WriteLine("ContactID: {0}", group.ContactID)
        Console.WriteLine("Order count: {0}", group.OrderCount)

        For Each orderInfo In group.orders
            Console.WriteLine("   Sale ID: {0}", orderInfo.SalesOrderID)
        Next

        Console.WriteLine("")
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<Contact> contacts = AWEntities.Contact;
    ObjectQuery<SalesOrderHeader> orders = AWEntities.SalesOrderHeader;

    var query = contacts.GroupJoin(orders, 
        contact => contact.ContactID,
        order => order.Contact.ContactID,
        (contact, contactGroup) => new
        {
            ContactID = contact.ContactID,
            OrderCount = contactGroup.Count(),
            Orders = contactGroup.Select(order => order)
        });

    foreach (var group in query)
    {
        Console.WriteLine("ContactID: {0}", group.ContactID);
        Console.WriteLine("Order count: {0}", group.OrderCount);
        foreach (var orderInfo in group.Orders)
        {
            Console.WriteLine("   Sale ID: {0}", orderInfo.SalesOrderID);
        }
        Console.WriteLine("");
    }
    }

Join

Beispiel

Im folgenden Beispiel werden die Tabellen Contact und SalesOrderHeader miteinander verknüpft.

Using AWEntities As New AdventureWorksEntities
    Dim contacts As ObjectQuery(Of Contact) = AWEntities.Contact
    Dim orders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader

    Dim query = _
        contacts.Join( _
            orders, _
            Function(ord) ord.ContactID, _
            Function(cont) cont.Contact.ContactID, _
            Function(cont, ord) New With _
                { _
                    .ContactID = cont.ContactID, _
                    .SalesOrderID = ord.SalesOrderID, _
                    .FirstName = cont.FirstName, _
                    .Lastname = cont.LastName, _
                    .TotalDue = ord.TotalDue _
                })

    For Each contact_order In query
        Console.WriteLine("ContactID: {0} " _
            & "SalesOrderID: {1} " & "FirstName: {2} " _
            & "Lastname: {3} " & "TotalDue: {4}", _
            contact_order.ContactID, _
            contact_order.SalesOrderID, _
            contact_order.FirstName, _
            contact_order.Lastname, _
            contact_order.TotalDue)
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<Contact> contacts = AWEntities.Contact;
    ObjectQuery<SalesOrderHeader> orders = AWEntities.SalesOrderHeader;

    var query =
        contacts.Join(
            orders,
            order => order.ContactID,
            contact => contact.Contact.ContactID,
            (contact, order) => new
            {
                ContactID = contact.ContactID,
                SalesOrderID = order.SalesOrderID,
                FirstName = contact.FirstName,
                Lastname = contact.LastName,
                TotalDue = order.TotalDue
            });

    foreach (var contact_order in query)
    {
        Console.WriteLine("ContactID: {0} "
                        + "SalesOrderID: {1} "
                        + "FirstName: {2} "
                        + "Lastname: {3} "
                        + "TotalDue: {4}",
            contact_order.ContactID,
            contact_order.SalesOrderID,
            contact_order.FirstName,
            contact_order.Lastname,
            contact_order.TotalDue);
    }
}

Beispiel

Im folgenden Beispiel werden die Tabellen Contact und SalesOrderHeader miteinander verknüpft, und die Ergebnisse werden nach der Kontakt-ID gruppiert.

Using AWEntities As New AdventureWorksEntities
    Dim contacts As ObjectQuery(Of Contact) = AWEntities.Contact
    Dim orders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader

    Dim query = _
        contacts.Join( _
            orders, _
            Function(ord) ord.ContactID, _
            Function(cont) cont.Contact.ContactID, _
            Function(cont, ord) New With _
                { _
                    .ContactID = cont.ContactID, _
                    .SalesOrderID = ord.SalesOrderID, _
                    .FirstName = cont.FirstName, _
                    .Lastname = cont.LastName, _
                    .TotalDue = ord.TotalDue _
                }) _
                .GroupBy(Function(record) record.ContactID)

    For Each group In query
        For Each contact_order In group
            Console.WriteLine("ContactID: {0} " _
                & "SalesOrderID: {1} " & "FirstName: {2} " _
                & "Lastname: {3} " & "TotalDue: {4}", _
                contact_order.ContactID, _
                contact_order.SalesOrderID, _
                contact_order.FirstName, _
                contact_order.Lastname, _
                contact_order.TotalDue)
        Next
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<Contact> contacts = AWEntities.Contact;
    ObjectQuery<SalesOrderHeader> orders = AWEntities.SalesOrderHeader;

    var query = contacts.Join(
        orders,
        order => order.ContactID,
        contact => contact.Contact.ContactID,
        (contact, order) => new
        {
            ContactID = contact.ContactID,
            SalesOrderID = order.SalesOrderID,
            FirstName = contact.FirstName,
            Lastname = contact.LastName,
            TotalDue = order.TotalDue
        })
            .GroupBy(record => record.ContactID);

    foreach (var group in query)
    {
        foreach (var contact_order in group)
        {
            Console.WriteLine("ContactID: {0} "
                            + "SalesOrderID: {1} "
                            + "FirstName: {2} "
                            + "Lastname: {3} "
                            + "TotalDue: {4}",
                contact_order.ContactID,
                contact_order.SalesOrderID,
                contact_order.FirstName,
                contact_order.Lastname,
                contact_order.TotalDue);
        }
    }
}

Siehe auch

Konzepte

Beispiele für die methodenbasierte Abfragesyntax (LINQ to Entities)