Export (0) Print
Expand All

Method-Based Query Syntax Examples: Join Operators

The examples in this topic demonstrate how to use the Join and GroupJoin methods to query the AdventureWorks Sales Model using method-based query syntax. The AdventureWorks Sales Model used in these examples is built from the Contact, Address, Product, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorks sample database.

The examples in this topic use the following using/Imports statements:

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;

GroupJoin

Example

The following example performs a GroupJoin over the SalesOrderHeader and SalesOrderDetail tables to find the number of orders per customer. A group join is the equivalent of a left outer join, which returns each element of the first (left) data source, even if no correlated elements are in the other data source.

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

    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);
    }

}

Example

The following example performs a GroupJoin over the Contact and SalesOrderHeader tables to find the number of orders per contact. The order count and IDs for each contact are displayed.

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

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

    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

Example

The following example performs a join over the Contact and SalesOrderHeader tables.

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

    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);
    }
}

Example

The following example performs a join over the Contact and SalesOrderHeader tables, grouping the results by contact ID.

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

    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);
        }
    }
}

See Also

Community Additions

ADD
Show:
© 2014 Microsoft