Let's Run Some Queries!
By the end of Part 1 we set the stage and looked at a couple of new features coming up in Orcas, but hadn't done anything particularly exciting and new with LINQ To Objects. In this Part I'll hopefully whet your appetite for LINQ a little bit more as we run some queries that are (a) very easy with LINQ and (b) nowhere near as easy with VB 2005 and earlier edition tools. (And later in Part 3 you'll really be able to see the future according to LINQ!)
Let's begin with a query that finds all the Person objects in our Person List who are under 21 and who live in Belgium. The LINQ version of such a query is:
Dim People() As Person
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles Button1.Click
Dim YoungBelgians = From P In People _
Where P.DateOfBirth > #12/12/1978# AndAlso P.Country = "Belgium" _
Select p
Console.WriteLine("Young Belgians:")
For Each Youth In YoungBelgians
Console.WriteLine(Youth.Forename & " " & Youth.Surname)
Next
End Sub
. . .
In the above example (and those that follow) I used a small collection of pre-created Person objects. If you want the code for this in order to try the queries yourself, it looks like this:
Function CreateDataVB9_3()
As Array
Dim SomePeople()
As Person = {
New Person
With {.Forename =
"Jan", .Surname =
"Urqhart", _
.DateOfBirth = #9/8/1959#, .Gender = Gender.Female, _
.Country =
"Belgium", .HomeTown =
"Ghent"}, _
New Person
With {.Surname =
"Fleischmann", .Forename =
"Heinje", .Country =
"Belgium", _
.Gender = Gender.Male, .DateOfBirth = #7/11/1982#, .HomeTown =
"Mons"}, _
New Person
With {.HomeTown =
"Leeds", .Forename =
"Ken", .Surname =
"Browning", _
.Gender = Gender.Male, .DateOfBirth = #3/9/1978#}, _
New Person
With {.Forename =
"Sandi", .Surname =
"Skeet", .DateOfBirth = #4/9/1981#, _
.Gender = Gender.Female, .Country =
"UK", .HomeTown =
"Manchester"}, _
New Person
With {.Forename =
"Janet", .Surname =
"Brent", _
.DateOfBirth = #9/12/1969#, .Gender = Gender.Female, _
.Country =
"Belgium", .HomeTown =
"Antwerp"}, _
New Person
With {.Forename =
"Ged", .Surname =
"Mead", .DateOfBirth = #11/24/1946#, _
.HomeTown =
"Dumfries", .Country =
"UK"}, _
New Person
With {.Forename =
"Sylvie", .Surname =
"Swanson", _
.DateOfBirth = #12/1/1977#, .Gender = Gender.Female, _
.Country =
"UK", .HomeTown =
"Manchester"}, _
New Person
With {.Forename =
"Freddie", .Surname =
"Foggin", .DateOfBirth = #4/14/1958#, _
.HomeTown =
"York", .Country =
"UK"}, _
New Person
With {.Forename =
"Johannes", .Surname =
"LeBon", _
.DateOfBirth = #10/8/1978#, .Gender = Gender.Male, _
.Country =
"Belgium", .HomeTown =
"Aachen"}, _
New Person
With {.Forename =
"William", .Surname =
"Birchley", _
.DateOfBirth = #7/8/1959#, .Gender = Gender.Male, _
.Country =
"UK", .HomeTown =
"Leeds"}, _
New Person
With {.Forename =
"Sarah", .Surname =
"Wilkinson", _
.DateOfBirth = #10/18/1990#, .Gender = Gender.Female, _
.Country =
"UK", .HomeTown =
"Manchester"}, _
New Person
With {.Forename =
"Frank", .Surname =
"Whittle", _
.DateOfBirth = #1/8/1993#, .Gender = Gender.Male, _
.Country =
"UK", .HomeTown =
"Manchester"}, _
New Person
With {.Forename =
"Natalie", .Surname =
"Hebber", _
.DateOfBirth = #2/28/1986#, .Gender = Gender.Female, _
.Country =
"Belgium", .HomeTown =
"Ghent"}, _
New Person
With {.Forename =
"Franc", .Surname =
"Mendler", _
.DateOfBirth = #10/8/1990#, .Gender = Gender.Male, _
.Country =
"Belgium", .HomeTown =
"Aachen"}, _
New Person With {.Forename = "Marc", .Surname = "Goosens", _
.DateOfBirth = #9/9/1989#, .Gender = Gender.Male, _
.Country = "Belgium", .HomeTown = "Ghent"}, _
New Person With {.Forename = "Pauline", .Surname = "Pearson", .DateOfBirth = #12/19/1989#, _
.Gender = Gender.Female, .Country = "UK", .HomeTown = "Leeds"}, _
New Person With {.Forename = "Jim", .Surname = "Burnley", .DateOfBirth = #1/29/1981#, _
.Gender = Gender.Male, .Country = "UK", .HomeTown = "Manchester"}}
Return SomePeople
End Function
. . .
And to populate the People array with that data, you would just call it with:
People = CreateDataVB9_3()
If you have read the first part of this article, you will know that the above code uses some of the new features that will be available in VB 9, in particular the extended use of the With keyword and a new style of array initialization.
Taking this query a step further, it is very easy to sort the list of Young Belgians alphabetically by surname:
Dim YoungBelgians = From P In People _
Where P.DateOfBirth > #12/12/1978# AndAlso P.Country = "Belgium" _
Order By P.Surname _
Select p
Console.WriteLine("Young Belgians:")
For Each Youth In YoungBelgians
Console.WriteLine(Youth.Surname & ", " & Youth.Forename)
Next
The resulting display will be:
Young Belgians:
Fleischmann, Heinje
Goosens, Marc
Hebber, Natalie
Mendler, Franc
By the way, here's a little Gotcha that's worth watching out for: If you try the code out and you see the following error:-
'Select Case' must end with a matching 'End Select'
this will be because you forgot to add the underscore at the end of the previous line. Been there, done that!
With LINQ you can sort and sub-sort all on one line. This time we will return all Persons and sort them first by Gender and then by Surnames within each Gender. The LINQ code is trivial:
Dim AllPersons = From P In People _
Order By P.Gender, P.Surname _
Select P
Console.WriteLine("All Persons (Sorted):")
For Each SortedPerson In AllPersons
Console.WriteLine(SortedPerson.Forename & " " & SortedPerson.Surname)
Next
The output from this looks like this:
All Persons (Sorted):
Janet Brent
Natalie Hebber
Pauline Pearson
Sandi Skeet
Sylvie Swanson
Jan Urqhart
Sarah Wilkinson
William Birchley
Ken Browning
Jim Burnley
Heinje Fleischmann
Marc Goosens
Johannes LeBon
Franc Mendler
Frank Whittle
Freddie Foggin
Ged Mead
The output is correct. First the Females(7), then the Males(8) and finally those that weren't assigned a value for Gender when the collection was created(2). But it's not particularly well formatted, is it? What would be good would be to group them, each with their Gender Heading. This will be possible using the "Group By" operator. Unfortunately at the time of writing this feature isn't implemented in VB, but I'll revisit this item and update it when it is - either via a later Beta or the final version when it is released.
For now, I'll settle for:
Dim AllPersons = From P In People _
Order By P.Gender, P.Surname _
Select P
Console.WriteLine("All Persons (Sorted):")
For Each SortedPerson In AllPersons
Console.WriteLine(SortedPerson.Forename & " " & _
SortedPerson.Surname & " : " & SortedPerson.Gender.ToString)
Next
which does make things clearer, even if it's not ideal.
It's also easy to have multiple Where operators in the same query. Take a case where you wanted to identify all those over 21 who live in either Leeds or Ghent. The query would be:
Dim AllPersons = From P In People _
Where P.HomeTown = "Leeds" _
Or P.HomeTown = "Ghent" _
Where P.DateOfBirth < #1/1/1987# _
Order By P.DateOfBirth Descending _
Select P
Notice also that this query sorts the names in date of birth descending order so the youngest person will be listed first, which is more intuitive. The following code snippet displays the result to the console. (The CalculateAge method isn't relevant to the LINQ query as such, but as it is available in the Person Class created earlier I've used it to improve the output for the user.)
Console.WriteLine("All Persons (Over 21 From Leeds and Ghent):")
For Each TwinTownPerson In AllPersons
Console.WriteLine(TwinTownPerson.Forename & " " & _
TwinTownPerson.Surname & " : " & CStr(Person.CalculateAge(TwinTownPerson.DateOfBirth, Now)) & " - " & TwinTownPerson.HomeTown)
Next
The output is:-
All Persons (Over 21 From Leeds and Ghent):
Natalie Hebber : 21 - Ghent
Ken Browning : 29 - Leeds
Jan Urqhart : 47 - Ghent
William Birchley : 47 - Leeds
Several of the more interesting operators are still not implemented at the time of writing. Group By, as mentioned earlier and also Join, which obviously is going to be a very powerful tool in LINQ queries. I have also seen samples which appear to use Into, but I haven't been able to implement that either in my March CTP version.
I plan to include samples that will demonstrate all of these and more as they come onstream. In the meantime, the next and (for the time being) final part of LINQ To Objects we will look at how to join two data sources and run a query against them.