Converting SQL to LINQ, Part 6: Joins (Bill Horst)

Converting SQL to LINQ, Part 6: Joins (Bill Horst)

  • Comments 16

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.

 

JOIN

 

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.

 

Cross Join

 

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.

 

SQL

SELECT CustomerTable.Name, OrderTable.OrderDate

FROM CustomerTable, OrderTable

 

 

VB

From Contact In CustomerTable, Shipment In OrderTable _

Select Contact.Name, Shipment.OrderDate

 

 

Inner Join

 

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.

 

SQL

SELECT Contact.Name, Shipment.OrderID

FROM CustomerTable Contact

INNER JOIN OrderTable Shipment

ON Contact.CustomerID = Shipment.CustomerID

AND Contact.Zip = Shipment.ShippingZip

 

 

VB

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.

 

SQL

SELECT Contact.Name, Shipment.OrderID

FROM CustomerTable Contact

INNER JOIN OrderTable Shipment

ON Contact.CustomerID < Shipment.CustomerID

 

 

VB

From Contact In CustomerTable, Shipment In OrderTable _

Where Contact.CustomerID < Shipment.CustomerID

Select Contact.Name, Shipment.OrderID

 

 

Natural Join

 

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.

 

SQL

SELECT * FROM CustomerTable

NATURAL JOIN OrderTable

 

 

VB

From Contact In CustomerTable _

Join Shipment In OrderTable _

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.

 

SQL

SELECT CustomerTable.Name, SUM(OrderTable.Cost) Sum

FROM CustomerTable

LEFT JOIN OrderTable

ON CustomerTable.CustomerID = OrderTable.CustomerID

GROUP BY CustomerTable.Name

 

 

VB

From Contact In CustomerTable _

Group Join Shipment In OrderTable _

On Contact.CustomerID Equals Shipment.CustomerID _

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.

 

SQL

SELECT *

FROM CustomerTable Contact

LEFT JOIN OrderTable Shipment

ON Contact.CustomerID = Shipment.CustomerID

 

 

VB

From Contact In CustomerTable _

Group Join Shipment In OrderTable _

On Contact.CustomerID Equals Shipment.CustomerID _

Into Group

 

 

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

Leave a Comment
  • Please add 3 and 1 and type the answer here:
  • Post
  • PingBack from http://msdnrss.thecoderblogs.com/2007/12/31/converting-sql-to-linq-part-6-joins-bill-horst-2/

  • Does LINQ have anything that emulates FULL OUTER JOIN?

  • Si quieres aprender LINQ para Visual Basic 2008, quizás te interesen los siguientes artículos que el

  • Si quieres aprender LINQ para Visual Basic 2008, quizás te interesen los siguientes artículos que el

  • I have reviewed all your SQL to LINQ blogs.  Thank you for those.  I look forward to additional topics.

    Back to Part 3 and the WHERE clause example.  What if you need to restrict to a "one of" in a list, for example:

    SQL Statement

    SELECT *

    FROM CustomerTable

    WHERE (State In ("WA","CO","AS","SC","NC","TN","VA","OR","WV","FL","MA","WY","GA","NH","PA","IL","AL"));

    This uses the "In" functionality of a SQL Where Clause.  I do not want to have to do an expression as individual ORs: State = "WA" OR State = "CO" OR State = "AS" OR ....

    A bunch of "ORs" is cumbersome when the list has many members.  The only thing I have been able to come up with is to put the states of interest into a collection with the state abbreviation as the key in the collection.  Then use the StatesOfInterest.Contains(State) as the boolean expression in the WHERE clause of the LINQ query.

       Dim StatesOfInterest As New Collection()

       StatesOfInterest.Add("", "WA")

       StatesOfInterest.Add("", "CO")

       StatesOfInterest.Add("", "AS")

       StatesOfInterest.Add("", "SC")

       StatesOfInterest.Add("", "NC")

       StatesOfInterest.Add("", "TN")

       StatesOfInterest.Add("", "VA")

       StatesOfInterest.Add("", "OR")

       StatesOfInterest.Add("", "WV")

       StatesOfInterest.Add("", "FL")

       StatesOfInterest.Add("", "MA")

       StatesOfInterest.Add("", "WY")

       StatesOfInterest.Add("", "GA")

       StatesOfInterest.Add("", "NH")

       StatesOfInterest.Add("", "PA")

       StatesOfInterest.Add("", "IL")

       StatesOfInterest.Add("", "AL")

       Dim CustQuery = From Contact In dsNWind1.CustomerTable _

                   Where StatesOfInterest.Contains(Contact.State)

    This seems to work, but there is the overhead of creating and populating the StatesOfInterest Collection.  Is there an easier way?

  • The following SQL Query was done in VB6 to open an ADODB.Recordset against a connection to an Access Database:

         SQLstr = "SELECT Sections.InitialStateON, Sections.PointID, Points.TypeID, Points.FeedBackID, " & vbCrLf & _

                           "Points.FeedBackDirID, Points.LogicID, Points.Address " & vbCrLf & _

                     "FROM Points RIGHT JOIN Sections ON Points.PointID = Sections.PointID " & vbCrLf & _

                     "Where ((Points.TypeID) In (2,3,4,5,6,8,9,11))" & vbCrLf & _

                     "ORDER BY Sections.OrderNum;"

    This is the LINQ statement that I have tried to convert it to:

         Dim SecDisplayQuery = From DispSectList In ds4DiagramArray.Sections _

                               Group Join DispPointList In ds4DiagramArray.Points On _

                                     DispSectList.PointID Equals DispPointList.PointID _

                               Into SectDispInfo = Group _

                               Where DispPointList.TypeID = 2 OrElse _

                                     DispPointList. TypeID = 3 OrElse _

                                     DispPointList. TypeID = 4 OrElse _

                                     DispPointList. TypeID = 5 OrElse _

                                     DispPointList. TypeID = 6 OrElse _

                                     DispPointList. TypeID = 8 OrElse _

                                     DispPointList. TypeID = 9 OrElse _

                                     DispPointList. TypeID = 11 _

                               Select SectDispInfo.InitialStateON, _

                                     SectDispInfo.PointID, _

                                     SectDispInfo.TypeID, _

                                     SectDispInfo.FeedBackID, _

                                     SectDispInfo.FeedBackDirID, _

                                     SectDispInfo.LogicID, _

                                     SectDispInfo.Address _

                               Order By SectDispInfo.OrderNum

    I can not get this LINQ to DataSet to be happy any way I have tried.  When using the Group Join, Intellisense wants an “Into” clause.  I am not clear on what the “Into” clause is for.  I am guessing that it is a temporary intermediate object that contains the joined data and could then be "Selected" from as I have tried to do in the “Select” clause.  Intellisense  recognized the alias “SectDispInfo” when I started the Select Clause, but then did not recognized what I thought would be the data columns.  This indicates to me that I am doing something wrong.  Also when writing the “Where” clause, intellisense presented me with the alias “DispSectList” but not “DispPointList” that is where the TypeID data is.  I must be doing something wrong there too.  Help!!!

    ds4DiagramArray is a DataSet created in the DataSet Designer with 2 related tables:

    Table: Sections

    Column: OrderNum as System.Int32

    Column: InitialStateON as System.Boolean

    Column: PointID as System.Int32

    Table: Points

    Column: PointID as System.Int32

    Column: TypeID as System.Int32

    Column: Address as System.String

    Column: FeedBackID as System.Int32

    Column: FeedBackDirID as System.Int32

    Column: LogicID as System.Int32

    The Sections table is a table of points to be displayed by this routine in a given order defined by OrderNum and is related to the Points table through the PointID column.

    This DataSet is populated using ADO.Net DataAdapters from an Access Database.  There are a lot more fields in the Access Database in both the Sections Table and the Points Table, but they are not needed for this routine in the project, so the DataAdapters only load a DataSet that is a subset of the DataBase.

  • Here&#39;s a summary of all the content the VB team members, including myself, have created for you on

  • Hi,

    Will you talk about subquery in the future?

    I want to output OderID, and the LATEST Order status of the order

    Table: Order

    Column: OderID

    Column: OrderDate

    Table: OrderStatus

    Column: OderStatusID

    Column: OrderID

    Column: OrderStatus

    Column: StatusDate

    There is a one to many relationship between Table Order and OrderStatus.

    This can easily be done using subquery, but how to do it in Linq without using stored procedure or user defined function in the database?

    Thanks

  • Answering all three questions:

    TC - I'm about to put together a post which will discuss subquery.  Please see Part 7.

    Bob - The short answer is No, we don't have built-in support for Full Outer Join.  There is a way to get the same results as a Full Outer Join, but it is fairly complicated.  I'll try and cover FULL OUTER JOIN and LEFT/RIGHT OUTER JOIN more thoroughly in Part 8 (hopefully around the 15th).

    Bill - If there are other specific topics you (or anyone else reading this) would like to see addressed, please suggest them in a comment.

    Regarding the In operator, I don't know of any way slicker than what you've already come up with to get the right result.  Unfortunately, we only had time to implement a certain set of query features for VB9.

    As stated above, I'll cover the outer joins more fully next week, since after talking to one of our developers I've got a better answer on how to mimic this with LINQ.  Until then, I got the following variation on your query to compile.  I hope to explain this all better in Linq to Sql Part 8.

    Dim SecDisplayQuery = _

               From DispSectList In ds4DiagramArray.Sections _

               Group Join DispPointList In ds4DiagramArray.Points On _

                   DispSectList.pointid Equals DispPointList.pointid _

               Into SectDispInfo = Group _

               From DispPointList In SectDispInfo.DefaultIfEmpty() _

               Where DispPointList.typeid = 2 OrElse _

                   DispPointList.typeid = 3 OrElse _

                   DispPointList.typeid = 4 OrElse _

                   DispPointList.typeid = 5 OrElse _

                   DispPointList.typeid = 6 OrElse _

                   DispPointList.typeid = 8 OrElse _

                   DispPointList.typeid = 9 OrElse _

                   DispPointList.typeid = 11 _

               Order By DispSectList.ordernum _

               Select DispSectList.initialstateon, _

                   DispSectList.pointid, _

                   DispPointList.typeid, _

                   DispPointList.feedbackid, _

                   DispPointList.feedbackdirid, _

                   DispPointList.logicid, _

                   DispPointList.address

  • This post assumes you’ve read the previous posts in this series: Converting SQL to LINQ, Part 1: The

  • This post assumes you’ve read the previous posts in this series. After my post on joins , I’ve had some

  • This post assumes you’ve read the previous posts in this series. After my post on joins , I’ve had some

  • ここでは、このシリーズの前の投稿をお読みになっていることを前提としています。 Converting SQL to LINQ, Part 1: The Basics ( 英語 ) Converting SQL

  • ここでは、このシリーズの 以前の投稿 ( 英語 ) をお読みになっていることを前提としています。 結合 について投稿した後で、外部結合について、いくつかの質問を受けました。パート 6 でご覧になったように、

  • How to left join multiple tables?

    Please show me the example.

    Thanks

Page 1 of 2 (16 items) 12