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

MSDN Blog Postings » Converting SQL to LINQ, Part 6: Joins (Bill Horst)

MSDN Blog Postings » Converting SQL to LINQ, Part 6: Joins (Bill Horst) — Tue, 01 Jan 2008 02:25:01 GMT

PingBack from http://msdnrss.thecoderblogs.com/2007/12/31/converting-sql-to-linq-part-6-joins-bill-horst-2/

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

Bob Beauchemin — Tue, 01 Jan 2008 03:15:33 GMT

Does LINQ have anything that emulates FULL OUTER JOIN?

Aprendiendo LINQ para Visual Basic 2008

Jorge Serrano - MVP Visual Developer - Visual Basic — Tue, 01 Jan 2008 13:00:03 GMT

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

Aprendiendo LINQ para Visual Basic 2008

El blog de Jorge — Tue, 01 Jan 2008 13:00:04 GMT

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

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

GMA Bill — Wed, 02 Jan 2008 01:07:45 GMT

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?

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

GMA Bill — Wed, 02 Jan 2008 23:02:21 GMT

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.

Content Rollup for November and December

Noticias externas — Fri, 04 Jan 2008 19:03:45 GMT

Here's a summary of all the content the VB team members, including myself, have created for you on

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

TC — Mon, 07 Jan 2008 05:08:15 GMT

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

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

Bill Horst — Tue, 08 Jan 2008 21:39:23 GMT

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

Converting SQL to LINQ, Part 7: UNION, TOP, Subqueries (Bill Horst)

The Visual Basic Team — Tue, 08 Jan 2008 23:44:53 GMT

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

Converting SQL to LINQ, Part 8: Left/Right Outer Join (Bill Horst)

The Visual Basic Team — Thu, 31 Jan 2008 22:42:35 GMT

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 8: Left/Right Outer Join (Bill Horst)

Noticias externas — Thu, 31 Jan 2008 23:42:10 GMT

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

SQL から LINQ への変換、パート 7 : UNION、TOP、サブクエリ (Bill Horst)

The Visual Basic Team — Thu, 29 May 2008 10:50:18 GMT

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

SQL から LINQ への変換、パート 8 : 左外部結合と右外部結合 (Bill Horst)

The Visual Basic Team — Fri, 06 Jun 2008 08:00:28 GMT

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