Although some of the queries we saw at the end of Part 2 are quite impressive - and light years easier than doing it with Loops and If/Then tests - there are still more improvements to come. Let's take the situation where you want to run Join queries across two separate data sources. This is something you'll commonly do with Access or SQL Server, etc, data sources but not something you'd automatically think of doing with, for example, a couple of collections.
To demonstrate this, we can use a collection of objects from the Person class that we created earlier, together with a new Class that contains a field or fields that we can join them on. The scenario I'm going to use is that we run courses in the UK and in Belgium, so we will have a class named "Course". Here is the code for that class:
Public Class Course
Private m_Title As String = "Not Known"
Private m_Location As String = "Not Known"
Private m_StartDate As DateTime = Today
Private m_DurationDays As Integer = 1
Property Title() As String
Get
Return m_Title
End Get
Set(ByVal value As String)
m_Title = value
End Set
End Property
Property Location() As String
Get
Return m_Location
End Get
Set(ByVal value As String)
m_Location = value
End Set
End Property
Property StartDate() As DateTime
Get
Return m_StartDate
End Get
Set(ByVal value As DateTime)
m_StartDate = value
End Set
End Property
Property DurationDays() As Integer
Get
Return m_DurationDays
End Get
Set(ByVal value As Integer)
m_DurationDays = value
End Set
End Property
Public Overrides Function ToString() As String
Return m_Title & " : " & m_Location
End Function
End Class
. . .
And, as I'm sure you'll realise, we will use the Location Property in the Course class as the field to join the HomeTown property of the Person class - the purpose of this imaginary scenario being that we can invite potential students to attend courses in their home town.
As we did in earlier examples for the Person class, we can create a simple array of Course objects:
Function CreateCourses() As Array
Dim SomeCourses() As Course = {New Course With {.Title = "VB 2005", _
.Location = "Leeds", .StartDate = New DateTime(2007, 8, 8), .DurationDays = 7}, _
New Course With {.Title = "Java", .Location = "Ghent", .StartDate = New DateTime(2007, 12, 1), _
.DurationDays = 14}, _
New Course With {.Title = "Ajax", .Location = "Manchester", .StartDate = New DateTime(2008, 1, 13), _
.DurationDays = 10}, _
New Course With {.Title = "Classic VB", .Location = "Leeds", .StartDate = New DateTime(2006, 2, 2), _
.DurationDays = 10}, _
New Course With {.Title = "SQL Server 2005", .StartDate = New DateTime(2008, 5, 6), _
.Location = "London", .DurationDays = 12}}
Return SomeCourses
End Function
. . .
Note that for demo purposes I've included a course location in London, a place which none of our current Persons have their HomeTown, and also a course start date which has already passed. We can use these for validation purposes in our queries.
First, we need to create the two arrays to query on:
Public Class Form1
Dim People() As Person
Dim Courses() As Course
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
' Get some data to work with
People = CreateDataVB9_3()
Courses = CreateCourses()
End Sub
And then we can run a query that matches all Persons whose HomeTowns are located in a place where we hold a course (reagrdless of course date):
Dim Students = From P In People, C In Courses _
Where P.HomeTown = C.Location _
Order By C.Location _
Select P.Forename, P.Surname, P.HomeTown, C.Title, C.Location
Console.WriteLine("Potential Students:")
For Each Potential In Students
With Potential
Console.WriteLine(String.Format("{0} {1} From {2} - {3} Course in {4}", .Forename, _
.Surname, .HomeTown, .Title, .Location))
End With
Next
A lot of this will be familiar from the earlier examples. We look into both the Persons and the Courses arrays and find those cases where the Person's HomeTown matches the Course's Location.
The resulting array is then ordered alphabetically by Location.
We select the details we are interested in - in this case the Person's name and home town, together with the Course's Title and Location.
(The selection of both HomeTown and Location is of course not really necessary. I've only done it this way so we can prove that the query has worked correctly by listing both the HomeTown and the Location properties separately in the output.)
The second block of code which outputs the results of the query to the console is very similar to output code we've used in previous examples. As I mentioned, I've included the place name twice simply to confirm that things are working as they should. The resulting output looks like this:
Potential Students:
Jan Urqhart From Ghent - Java Course in Ghent
Natalie Hebber From Ghent - Java Course in Ghent
Marc Goosens From Ghent - Java Course in Ghent
Ken Browning From Leeds - VB 2005 Course in Leeds
Ken Browning From Leeds - Classic VB Course in Leeds
William Birchley From Leeds - VB 2005 Course in Leeds
William Birchley From Leeds - Classic VB Course in Leeds
Pauline Pearson From Leeds - VB 2005 Course in Leeds
Pauline Pearson From Leeds - Classic VB Course in Leeds
Sandi Skeet From Manchester - Ajax Course in Manchester
Sylvie Swanson From Manchester - Ajax Course in Manchester
Sarah Wilkinson From Manchester - Ajax Course in Manchester
Frank Whittle From Manchester - Ajax Course in Manchester
Jim Burnley From Manchester - Ajax Course in Manchester
which seems to return the results you would expect, ordered by Location. As noted in a previous Part of this article, a GroupBy operator will also be available at a later stage in the evolution of LINQ in Orcas.
Finally, just to make use of that course I created where the start and finish dates have already passed, this query will only pull matches for future courses:
Private Sub FindCurrentCourseMatches()
Dim Students = From P In People, C In Courses _
Where P.HomeTown = C.Location _
Where C.StartDate > DateTime.Today _
Order By C.Location _
Select P.Forename, P.Surname, P.HomeTown, C.Title, C.StartDate
Console.WriteLine("Potential Students:")
For Each Potential In Students
With Potential
Console.WriteLine(String.Format("{0} {1} From {2} - {3} Course, Starting on {4}", .Forename, _
.Surname, .HomeTown, .Title, .StartDate.ToLongDateString))
End With
Next
End Sub
You may have noticed that this time I didn't include the Course Location in the Select line.
The resulting output is:
Potential Students:
Jan Urqhart From Ghent - Java Course, Starting on 12 December 2007
Natalie Hebber From Ghent - Java Course, Starting on 12 December 2007
Marc Goosens From Ghent - Java Course, Starting on 12 December 2007
Ken Browning From Leeds - VB 2005 Course, Starting on 08 August 2008
William Birchley From Leeds - VB 2005 Course, Starting on 08 August 2008
Pauline Pearson From Leeds - VB 2005 Course, Starting on 08 August 2008
Sandi Skeet From Manchester - Ajax Course, Starting on 13 January 2008
Sylvie Swanson From Manchester - Ajax Course, Starting on 13 January 2008
Sarah Wilkinson From Manchester - Ajax Course, Starting on 13 January 2008
Frank Whittle From Manchester - Ajax Course, Starting on 13 January 2008
Jim Burnley From Manchester - Ajax Course, Starting on 13 January 2008
- always assuming that you are reading this before the end of 2007, that is!
So, we've scratched the surface of LINQ To Objects and I hope I've succeeded in giving you a flavour of the possibilities.