Query Expression Syntax Examples: Join Operators (LINQ to Entities)
Joining is an important operation in queries that target data sources that have no navigable relationships to each other, such as relational database tables. A join of two data sources is the association of objects in one data source with objects that share a common attribute in the other data source. For more information, see Standard Query Operators Overview.
The examples in this topic demonstrate how to use the GroupJoin and Join methods to query the AdventureWorks Sales Model using query expression 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 = _
From order In orders _
Group Join detail In details _
On order.SalesOrderID _
Equals detail.SalesOrderID Into orderGroup = Group _
Select 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 =
from order in orders
join detail in details
on order.SalesOrderID
equals detail.SalesOrderID into orderGroup
select 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 = _
From contact In contacts _
Group Join order In orders _
On contact.ContactID _
Equals order.Contact.ContactID Into contactGroup = Group _
Select 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 =
from contact in contacts
join order in orders
on contact.ContactID
equals order.Contact.ContactID into contactGroup
select 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("");
}
}
Example
The following example performs a GroupJoin over the Contact and SalesOrderHeader tables. 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 contacts As ObjectQuery(Of Contact) = AWEntities.Contact
Dim orders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader
Dim query = _
From contact In contacts _
Group Join order In orders _
On contact.ContactID _
Equals order.Contact.ContactID Into contactGroup = Group _
Select 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 =
from contact in contacts
join order in orders
on contact.ContactID
equals order.Contact.ContactID into contactGroup
select 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 SalesOrderHeader and SalesOrderDetail tables to get online orders from the month of August.
Using AWEntities As New AdventureWorksEntities
Dim orders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader
Dim details As ObjectQuery(Of SalesOrderDetail) = AWEntities.SalesOrderDetail
Dim query = _
From ord In orders _
Join det In details _
On ord.SalesOrderID Equals det.SalesOrderID _
Where ord.OnlineOrderFlag = True _
And ord.OrderDate.Month = 8 _
Select New With _
{ _
.SalesOrderID = ord.SalesOrderID, _
.SalesOrderDetailID = det.SalesOrderDetailID, _
.OrderDate = ord.OrderDate, _
.ProductID = det.ProductID _
}
For Each ord In query
Console.WriteLine("{0}" & vbTab & "{1}" & vbTab & "{2:d}" & vbTab & "{3}", _
ord.SalesOrderID, _
ord.SalesOrderDetailID, _
ord.OrderDate, _
ord.ProductID)
Next
End Using
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
ObjectQuery<SalesOrderHeader> orders = AWEntities.SalesOrderHeader;
ObjectQuery<SalesOrderDetail> details = AWEntities.SalesOrderDetail;
var query =
from order in orders
join detail in details
on order.SalesOrderID equals detail.SalesOrderID
where order.OnlineOrderFlag == true
&& order.OrderDate.Month == 8
select new
{
SalesOrderID = order.SalesOrderID,
SalesOrderDetailID = detail.SalesOrderDetailID,
OrderDate = order.OrderDate,
ProductID = detail.ProductID
};
foreach (var order in query)
{
Console.WriteLine("{0}\t{1}\t{2:d}\t{3}",
order.SalesOrderID,
order.SalesOrderDetailID,
order.OrderDate,
order.ProductID);
}
}