Method-Based Query Syntax Examples: Join Operators (LINQ to Entities)
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:
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;
For more information, see How to: Create a LINQ to Entities Project in Visual Studio.
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 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);
}
}
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 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
Example
The following example performs a join over the Contact and SalesOrderHeader tables.
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);
}
}
Example
The following example performs a join over the Contact and SalesOrderHeader tables, grouping the results by contact ID.
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);
}
}
}