A group blog from members of the VB team
This post assumes you’ve read the previous posts in this series:
Converting SQL to LINQ, Part 1: The Basics
Converting SQL to LINQ, Part 2: FROM and SELECT
Converting SQL to LINQ, Part 3: DISTINCT, WHERE, ORDER BY and Operators
Converting SQL to LINQ, Part 4: Functions
Converting SQL to LINQ, Part 5: GROUP BY and HAVING
This post will discuss Cross Join, Inner Join, Natural Join and Outer (Left/Right) Joins.
It’s very common to query over more than one set of data (such as a table) in the same SQL SELECT statement. Bringing together the information in multiple tables is called a join, and there are several kinds of joins in both SQL and LINQ.
The simplest join is a Cross Join, or Cartesian Join, which is a many-to-many join between two sets of data. Each record in one set of data is joined to each record in another set. In a SQL SELECT statement, this is done by specifying more than one table in a FROM clause. In a VB LINQ expression, the same is true, as shown below.
SELECT CustomerTable.Name, OrderTable.OrderDate
FROM CustomerTable, OrderTable
From Contact In CustomerTable, Shipment In OrderTable _
Select Contact.Name, Shipment.OrderDate
An Inner Join is a one-to-one join, where records in one set of data are matched up with records in another set of data based on certain common fields. In a SQL SELECT statement, the second set of data is specified in an INNER JOIN clause, and the equalities used to join them are specified in an ON clause. Similarly, the second set of data in a VB LINQ Join is specified in a Join clause, and an On clause is used to specify which field to match up with the Equals operator.
SELECT Contact.Name, Shipment.OrderID
FROM CustomerTable Contact
INNER JOIN OrderTable Shipment
ON Contact.CustomerID = Shipment.CustomerID
AND Contact.Zip = Shipment.ShippingZip
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
The above example is an Equi-Join, meaning an equality operator is used to join information between the tables. This is the only operator allowed in the On clause, so to emulate any other join operators (such as less-than), you will need to use a Cross Join filtered with a Where clause.
ON Contact.CustomerID < Shipment.CustomerID
Where Contact.CustomerID < Shipment.CustomerID
A Natural Join is a one-to-one join where records in one set of data are matched up with records in another set of data based on all common fields (determined by matching names). In a SQL SELECT statement, the second set of data can be specified in a NATURAL JOIN clause, and the equalities used to join the tables are implicit. There is no direct equivalent to a natural join in VB LINQ expressions, so the best way to emulate it is to create an inner join and specify all common field equalities in the On clause manually. This is more verbose than the SQL version, but should be pretty straightforward.
SELECT * FROM CustomerTable
NATURAL JOIN OrderTable
From Contact In CustomerTable _
On Contact.CustomerID Equals Shipment.CustomerID _And Contact.Phone Equals Shipment.Phone
Outer (Left/Right) Join
An Outer Join (also known as a Left Join or Right Join) is a one-to-many join, where each record in one set of data can be matched up with multiple records in another set of data, based on common fields. In a SQL SELECT statement, the second set of data is specified in a LEFT JOIN or RIGHT JOIN clause, and the equalities used to join them are specified in an ON clause. In a LEFT JOIN, every record in the first (left) set of data is joined with all records in the second set of data that match it based on the join expressions. Every record in this first set of data will appear in the result, whether or not it matches anything in the second set. This is reversed in a RIGHT JOIN, where everything in the second (right) set of data appears and is matched to everything possible in the first set.
The closest VB LINQ construct to an Outer Join would be a Group Join. A Group Join clause specifies a second set of data, and provides the equality expressions in the On clause, much like a Join (Inner Join), described above. There is also an Into clause, which can be used to specify aggregates to calculate over each group, much like in a Group Join clause above.
Similar to a SQL LEFT JOIN, each member of the first set of data is matched with everything that matches it in the second set of data. Again, if there is no match for an item in the first set of data, it will still appear in the results.
SELECT CustomerTable.Name, SUM(OrderTable.Cost) Sum
LEFT JOIN OrderTable
ON CustomerTable.CustomerID = OrderTable.CustomerID
GROUP BY CustomerTable.Name
Group Join Shipment In OrderTable _
Into Sum(Shipment.Cost) _
Select Contact.Name, Sum
The best way to convert a RIGHT JOIN is to use this Group Join clause with the tables reversed (put the last one first).
It may be that there are times an Outer Join will be needed without aggregate functions. Using a Group keyword, as shown below, causes an array to be returned with the results in a particular group. This is probably the closest VB LINQ equivalent to an Outer Join without aggregate functions.
FROM CustomerTable Contact
LEFT JOIN OrderTable Shipment
These Group Join examples may not cover all the LEFT JOIN and RIGHT JOIN scenarios you’ll encounter, but hopefully they will give you the understanding necessary to create a LINQ query that matches your existing SQL query.
Next week, I plan to cover a “grab bag” of topics, including UNION, TOP and Sub-queries.
If there are any additional SQL SELECT constructs you’d like to see in LINQ, please add a comment to this post about it.
- Bill Horst, VB IDE Test
itms = (From mm In dc.DriverDevices
Group Join ji In dc.JobImages On mm Equals Int(ji.DriverDevicesId)
Into pl = Group From ji In pl.DefaultIfEmpty
Select ID = mm.ID,
DriverId1 = mm.DriverID,
UUID = mm.UUID,
StartDate1 = mm.StartDate,
EndDate1 = mm.EndDate,
latitude = mm.latitude,
longitude = mm.longitude,
LocationAddress = mm.LocationAddress,
RecordStatus = mm.RecordStatus,
JobImage = ji.JobImage).ToList
I got data but it not allowed to user ad giving error like
Public member 'StartDate' on type 'VB$AnonymousType_1(Of Integer,Integer,String,Date,Date,Nullable(Of Double),Nullable(Of Double),String,String,String)' not found.
please solved it