Joins with LINQ

This week I delivered a course, namely Introduction to ASP .NET 3.5. One of the participants asked me a question on SQL JOINS with LINQ. The request is to demontrate in VB the way to code three types of JOINS: CROSS JOIN, INNER JOIN, and OUTER JOIN. I thought would be interesting if I blog on my findings.

1. Cross Join

In SQL, you would code like:
SELECT CustomerTable.Name, OrderTable.OrderDate
FROM CustomerTable, OrderTable

Similarly, in VB you can code like:
From Contact In CustomerTable, Shipment In OrderTable _
Select Contact.Name, Shipment.OrderDate

2. Inner Join

In SQL, you would code like:
SELECT Contact.Name, Shipment.OrderID
FROM CustomerTable Contact
INNER JOIN OrderTable Shipment
ON Contact.CustomerID = Shipment.CustomerID
AND Contact.Zip = Shipment.ShippingZip

Similarly, in VB you can code like:
From Contact In CustomerTable
Join Shipment In OrderTable _
On Contact.CustomerID Equals Shipment.CustomerID _
And Contact.Zip Equals Shipment.ShippingZip _
Select Contact.Name, Shipment.OrderID

3. Outer (Left/Right) Join

In SQL, you would code like:
SELECT CustomerTable.Name, SUM(OrderTable.Cost) Sum
FROM CustomerTable
LEFT JOIN OrderTable
ON CustomerTable.CustomerID = OrderTable.CustomerID
GROUP BY CustomerTable.Name

Similarly, in VB you can code like:
From Contact In CustomerTable _
Group Join Shipment In OrderTable _
On Contact.CustomerID Equals Shipment.CustomerID _
Into Sum(Shipment.Cost) _
Select Contact.Name, Sum

Note: I’m using Northwind database.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: