SQL: ISNULL and COALESCE

It frequently comes up when teaching SQL about when to use the function ISNULL or the function COALESCE when writing code in SQL.

On an initial view it looks like both functions do the same thing except that COALESCE takes more arguments.

ISNULL(expression, replacement)

COALESCE(expression1, expression2 [,.... more expressions])

Let's do an example. Firstly we need some data

CREATE TABLE Person (
   Name varchar(20) NOT NULL,
   Postcode varchar(10) NULL
)
GO

INSERT INTO Person VALUES ('Karen', 'PA2 8NR')
INSERT INTO Person VALUES ('Julie', NULL)
INSERT INTO Person VALUES ('Mauda', NULL)
INSERT INTO Person VALUES ('Ali', NULL)
INSERT INTO Person VALUES ('Heidi', '2000')
INSERT INTO Person VALUES ('Helen', NULL)
INSERT INTO Person VALUES ('Rhona', 'G12 9SR')
GO

And if we run the following SQL we get the same results

SELECT Name, ISNULL(Postcode, 'None') FROM Person
SELECT Name, COALESCE(Postcode, 'None') FROM Person

Name                 
-------------------- ----------
Karen                PA2 8NR
Julie                None
Mauda                None
Ali                  None
Heidi                2000
Helen                None
Rhona                G12 9SR

The most obvious first difference is that ISNULL takes two parameters - an expression to check against (which can be any type), and an expression to return if the first parameter is NULL. The type of the replacement must be the same as the first expression or implicitly convertible to it. So the following can be done

SELECT Name, ISNULL(Postcode, 0) FROM Person

And will return the string '0' - converting the number 0 to a string. Doing this with COALESCE will

SELECT Name, COALESCE(Postcode, 0) FROM Person

Gives the following error message

Name                 
-------------------- -----------
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'PA2 8NR' to data type int.

Another point related to types will be apparent with this code sample

SELECT Name, ISNULL(Postcode, 'No Postcode') FROM Person
SELECT Name, COALESCE(Postcode, 'No Postcode') FROM Person

The output looks like this

Name                 
-------------------- ----------
Karen                PA2 8NR
Julie                No Postcod
Mauda                No Postcod
Ali                  No Postcod
Heidi                2000
Helen                No Postcod
Rhona                G12 9SR

Name                 
-------------------- -----------
Karen                PA2 8NR
Julie                No Postcode
Mauda                No Postcode
Ali                  No Postcode
Heidi                2000
Helen                No Postcode
Rhona                G12 9SR

The second set of results shows the output from COALESCE - which has the full text of "No postcode" whereas ISNULL has truncated it (to 10 characters) - which is the type of the column specified (Postcode) which is varchar(10).

The type has another knock on effect you might need to be aware of. If you use the type in a computed column then ISNULL will use the type of the first column (and the type created will be non-nullable). Let's create a table with as computed column. When you do this then you do not specify the type - SQL will infer the type from the result of the expression.

In the case of ISNULL this will be the type of the first parameter.

CREATE TABLE Person2 (
   Name varchar(20) NOT NULL,
   Postcode varchar(10) NULL,
   NotNullPostcode AS ISNULL(Postcode, 'None'))
GO
select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='Person2' AND COLUMN_NAME='NotNullPostcode'

COLUMN_NAME     DATA_TYPE       CHARACTER_MAXIMUM_LENGTH IS_NULLABLE
--------------- --------------- ------------------------ -----------
NotNullPostcode varchar         10                       NO

Even if you change the literal 'None' to something greater than 10 characters then it will only ever be 10 characters (the type of the first expression - in this case the type of the column Postcode).

Doing this with Coalesce will take the result of the COALESCE statement.

CREATE TABLE Person3 (
   Name varchar(20) NOT NULL,
   Postcode varchar(10) NULL,
   NotNullPostcode AS COALESCE(Postcode, 'None'))
GO
select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='Person3' AND COLUMN_NAME='NotNullPostcode'

COLUMN_NAME     DATA_TYPE       CHARACTER_MAXIMUM_LENGTH IS_NULLABLE
--------------- --------------- ------------------------ -----------
NotNullPostcode varchar         10                       YES

First thing to note is that even though we have specified a literal ('None') - the column is set as being Nullable. COALESCE will always be thought of returning a NULL - if any expression in the list can return a NULL. If all expressions are Non-Nullable then the resultant column won't allow nulls. Why you would then use COALESCE is then in doubt?

The Postcode column is longer than the literal 'None' - so the size is set to 10. Now let's make the literal larger in size.

CREATE TABLE Person3 (
   Name varchar(20) NOT NULL,
   Postcode varchar(10) NULL,
   NotNullPostcode AS COALESCE(Postcode, 'No postcode has been specified'))
GO
select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='Person3' AND COLUMN_NAME='NotNullPostcode'

COLUMN_NAME     DATA_TYPE       CHARACTER_MAXIMUM_LENGTH IS_NULLABLE
--------------- --------------- ------------------------ -----------
NotNullPostcode varchar         30                       YES

The type of an expression (especially the null-ability) is important when used in queries as the execution plan (how SQL thinks the best way to execute it) will be different.

As we have said the main difference between COALESCE and ISNULL is the number of parameters. COALESCE will look at each parameter (in order) and return the first expression that doesn't evaluate to NULL (or NULL if they all evaluate to NULL).

COALESCE is converted by SQL into a CASE statement. Thus the SQL below

SELECT Name, COALESCE(Postcode, 'None') FROM Person

is the same as

SELECT Name,
  CASE
     WHEN(Postcode IS NOT NULL) THEN Postcode
  ELSE 'None'
  END
FROM Person

This means that each expression is evaluated at least once and one may be evaluated a second time (in this case if Postcode is not null then it is evaluated to determine if it is not null and then evaluated again to return it). Another downside of this is that if you have an expression with sub-queries you may get different answers when the sub-query is evaluated a second time. ISNULL on the other hand is evaluated only once.

When to use?

An understanding of the differences of ISNULL and COALESCE is useful for the occasions when the differences are important. But why use them at all.

One use is the one given above - change some text. In this case when you don't know the postcode of the person. Remember when you deal with Nulls the result of an expression containing a NULL is also a NULL. So if you have an address as well you cannot concatenate the strings.

Additionally, aggregate functions (such as Average) will work with all the rows where the column in question is Not Null. Consider this example

CREATE Table Numbers (Number float NULL)
GO

INSERT INTO Numbers VALUES (4)
INSERT INTO Numbers VALUES (2)
INSERT INTO Numbers VALUES (3)
INSERT INTO Numbers VALUES (NULL)
INSERT INTO Numbers VALUES (6)
INSERT INTO Numbers VALUES (8)

The sum of the numbers is 23. If you run this SQL to ask for the average

SELECT AVG(Number) FROM Numbers

You get

4.6

Which is the result of 23/5. But using ISNULL to check Number first

SELECT AVG(ISNULL(Number,0)) FROM Numbers

Will now give you

3.83333333333333

Which is 23/6

C# Part 3: When are objects the same? Exploration of Equals, GetHashCode and IEquatable

The first two parts exploring equality looked at

• The Equals methods
• Using IEquatable<T>

This part will look at what GetHashCode() does. Quick summary first, we have this class

public class Employee : IEquatable<employee>
{
    private string employeeName;
    private int employeeNumber;

    public Employee(string employeeName, int employeeNumber)
    {
        this.employeeName = employeeName;
        this.employeeNumber = employeeNumber;
    }

    public override bool Equals(object obj)
    {
        if (obj == null)
            return false;

        Employee other = obj as Employee;
        if (other == null)
            return false;
 
        if (this.employeeNumber == other.employeeNumber)
            return true;
        else
            return false;
    }

    public bool Equals(Employee obj)
    {
        if (obj == null)
            return false;

        if (this.employeeNumber == obj.employeeNumber)
            return true;
        else
            return false;
    }
}

As we have overridden the Equals method (from System.Object) we are now getting a compilation working about GetHashCode()

"'Employee' overrides Object.Equals(object o) but does not override Object.GetHashCode()"

Anytime that we override Equals we should override GetHashCode(). But what does GetHashCode() do. Let's create a test - we aren't actually going to put a test within it, but do some "debugging".

[TestMethod]
public void MyTestMethod()
{
    TestContext.WriteLine("HashCode andrew {0}", andrew.GetHashCode());
    TestContext.WriteLine("HashCode rhona {0}", rhona.GetHashCode());
    TestContext.WriteLine("HashCode rhonda {0}", rhonda.GetHashCode());
}

We are using the TestContext to get some output. Run just this test, then click on the Test in the Test Results. You should see

The entries for all three object have a different "Hash number". The hash number is simply an integer. Before looking at the GetHashCode() method let's create another test using a HashSet. A HashSet contains a set of values that contain no duplicates. Firstly, let's create a HashSet of integers, where we add the same number twice. Our expectation will be that it will not be added to the HashSet a second time. So with a test we have

[TestMethod]
public void HashSetofIntegerTest()
{
    HashSet<int> numbers = new HashSet<int>();
    numbers.Add(1);
    numbers.Add(2);
    numbers.Add(3);
    numbers.Add(1);

    Assert.AreEqual(3, numbers.Count);
}

And when run it passes - despite making four calls to Add we only three elements. We don't get an error when we add - although the Add method returns a bool which when true is returned the element has added (or false if it is already in the set). Now let's do this with a HashSet<Employee>

[TestMethod]
public void HashSetOfEmployeeTest()
{
    HashSet<employee> workers = new HashSet<employee>();
    workers.Add(andrew);
    workers.Add(rhonda);
    workers.Add(rhona);

    Assert.AreEqual(2, workers.Count);
}

Running this test fails - all three employees have been added. However, we want only two people in it (as rhonda and rhona should be the the same person). If you step through the add methods it will just step to the next Add statement. The Add method is not calling our Equals method - when adding to our HashCode that only should have unique entries is not checking what is in the HashSet. Or is it? Now add a GetHashCode method (type public override and then select GetHashCode()). But leave the method with the default one created

public override int GetHashCode()
{
    return base.GetHashCode();
}

Now when you step through this the first thing (in the Employee class) that it does is call GetHashCode(). In the window for Call Stack - right click and you can choose "Show External Code". This will show the stack when the Add method is run, showing a call to a method "AddIfNotPresent" which then retrieves the HashCode.

So each time Add is being called GetHashCode is executed - but our Equals (methods) have not being executed. A Hash code is a numeric value which can be used to determine if two objects are not the same - but is can't be used to tell if two items are the same. Hash functions can be used for indexes. The rules are

• Two objects that are the same should return the same hash code
• Two objects that return the same hash code are not necessarily the same object
• GetHashCode() should be consistent and return the same hash code for the same data

So in this example, objects rhona and rhonda should return the same hash code. Object andrew could still return the same as rhona and rhonda - but this doesn't mean it is the same object. If we change GetHashCode to return the same value, we can see what it does then. So GetHashCode() will look like

public override int GetHashCode()
{   
    return 1;
}

Now run our test of the HashSet - it passes. There are only two objects in it. Now if you step through the code you will see

• GetHashCode()is called for the first object (andrew). The object will be added to the HashSet (the Count property will increment to 1).
• GetHashCode()is called for the second object (rhonda). Then the Equals method is called using the object for andrew with the "other" object being "rhonda". The two objects don't match so the object will be added to the HashSet (the Count property will increment to 2).
• GetHashCode()is called for the third object (rhona), and then Equals is called using the object for rhonda with the "other" object being "rhona".  The Equals method returns true - so the object isn't added.

In this case GetHashCode() is returning 1 for all objects. This isn't ideal - if there are lots of objects in our HashSet the Equals will be run for everyone. Each time an object is added GetHashCode()is called for that object and an index (of some sort) is created. Any object that returns a hash code that isn't in this index can be added to the HashSet without running the Equals method - we know if the hash code doesn't exist it is a different object. So returning just 1 isn't ideal. We need to return something that can identifies the employee number, such as

public override int GetHashCode()
{
    return this.employeeNumber.GetHashCode();
}

Check that the test still passes. And if we go back to our code to output the HashCodes we see that rhona and rhonda both return the same value

C# Part 2: When are objects the same? Exploration of Equals, GetHashCode and IEquatable

Following on from the previous post which looked at the Equals method overridden from System.Object I will now look at using the alternative version of Equals. So far we have a class Employee which looks like this

public class Employee 
{
    private string employeeName;
    private int employeeNumber;

    public Employee(string employeeName, int employeeN
    {
        this.employeeName = employeeName;
        this.employeeNumber = employeeNumber;
    }

    public override bool Equals(object obj)
    {
        if (obj == null)
            return false;

        Employee other = obj as Employee;
        if (other == null)
            return false;

        if (this.employeeNumber == other.employeeNumbe
            return true;
        else
            return false;
    }

    public bool Equals(Employee obj)
    {
        if (obj == null)
            return false;

        if (this.employeeNumber == obj.employeeNumber)
            return true;
        else
            return false;
    }
}

We wrote some tests, the ones we are interested in this post are

[TestMethod]
public void RhonaIsRhondaTest()
{
    Assert.AreEqual(rhona, rhonda);
}

[TestMethod]
public void RhonaIsRhondaUsingEqualsTest()
{
    Assert.IsTrue(rhona.Equals(rhonda));
}

Both methods pass. However, the first test (using Assert.AreEqual) will execute the Equals method with an object as parameter - the one that overrides the virtual method in System.Object.

In our next test we are going to put two of our employees into a List (andrew and rhonda) and then see if rhona is in the list? Which it should be?

[TestMethod]
public void ContainsInListTest()
{
    List workers = new List<Employee>();
    workers.Add(andrew);
    workers.Add(rhonda);

    Assert.IsTrue(workers.Contains(rhona));
}

The test passes - but if you then run through the debugger it is calling the Equals method inherited from System.Object. Let us now have our Employee class implement the interface IEquatable (or IEquatable<Employee>). This interface is defined as

public interface IEquatable<T>
{
    bool Equals(T other);
}

So the method we have created for Equals(Employee obj) matches this signature. Check the three tests above still pass. The only test that has changed is the call to ContainsTo in the list - which now calls the method as defined by IEquatable. This interface is used by generic collections (such as List).

The next post will look at GetHashCode() and using a HashSet<T>.

C# Part 1: When are objects the same? Exploration of Equals, GetHashCode and IEquatable

When I teach inheritance to an apprentice group we talk about overriding the ToString() method from System.Object. But I also get asked about overriding Equals (and then the subsequent need to override GetHashCode). This is a little out of the scope of the teaching - what is happening with overriding ToString() is difficult enough. Discussing what is equality is another step, which hopefully I can address here.

I am also going to use unit tests to prove/disprove what we think is going on.

We think of things that are equal contain the same values - or have something within them to make them equal. However, equality (for reference types) generally by default means that it is the same object (the string class is different - two strings are the same if they have the same value, but although strings are a reference type they generally work like a value type).

Firstly let's create a class definition for an employee that has two fields - the employee's name and the employee's number (which for the sake of this example is able to uniquely identify the employee).

public class Employee
{
    private string employeeName;
    private int employeeNumber;

    public Employee(string employeeName, int employeeNumber)
    {
        this.employeeName = employeeName;
        this.employeeNumber = employeeNumber;
    }
}

Now create a unit test (right click on the class and choose Create Unit Tests and follow the wizard).
Let's declare some fields and initialise them in the MyTestInitialize method (in the commented and hidden "Additional test attributes" section).

private Employee andrew;
private Employee rhona;
private Employee rhonda;

[TestInitialize()]
public void MyTestInitialize()
{
    andrew = new Employee("andrew", 2521);
    rhona = new Employee("rhona", 2791);
    rhonda = new Employee("rhonda", 2791);
}

Now employees rhona and rhonda are the same employee - just Rhona's name has been misspelt. But the employee numbers match. Let's write some tests to say that andrew isn't rhona or rhonda but rhona is the same as rhonda.

[TestMethod]
public void AndrewIsntRhonaOrRhondaTest()
{
    Assert.AreNotEqual(andrew, rhona);
    Assert.AreNotEqual(andrew, rhonda);
}

The first test method we write passes (as expected). Before we write some tests to check rhona against rhonda - let's just confirm that the references are as expected. In this case rhona and rhonda are separate objects, as well as just showing that if we copy a reference to an object that this is the same. Here we will use System.Object.ReferenceEquals method.

[TestMethod]
public void CheckReferencesAreAsExpectedTest()
{
    Employee e = andrew;
    Assert.AreEqual(e, andrew);
    Assert.IsTrue(Object.ReferenceEquals(e, andrew));
    Assert.IsFalse(Object.ReferenceEquals(rhona, rhonda));
}

Run this test and it works. Now what about Rhona and Rhonda - we want them to be the same person, so lets write a test for this

[TestMethod]
public void RhonaIsRhondaTest()
{
    Assert.AreEqual(rhona, rhonda);
}

But this test fails - as expected. The objects rhona and rhonda are separate objects. We wish them to be treated as if they are equal. Before we do this there are a couple of ways that we can do this - using the Equals method and also using the "==" operator (which we won't worry about). So lets write a test to check. Hopefully they should all fail. (It may well be that Assert.AreEqual as above calls Equals, but I'm not sure...)

[TestMethod]
public void RhonaIsRhondaUsingEqualsTest()
{
    Assert.IsTrue(rhona.Equals(rhonda));
}

Now the tests for Rhona being Rhonda fail - as expected. We need to write some code. Any class that we want to use to represent a value should override Equals. Go back to the class and add a method to override Equals - if you type public override the intellisense will then be given a list of methods you can override. By default the code looks like.

public override bool Equals(object obj)
{
    return base.Equals(obj);
}

If you try to compile you will receive a warning

"'Employee' overrides Object.Equals(object o) but does not override Object.GetHashCode()".

For the moment we are going to ignore this (and explain this later). The Equals method takes one parameter (obj) and returns a bool. When overriding Equals one thing you must ensure is that if you compare the current instance to the other instance and the other instance is null, it should return false. So first thing, lets write a test

[TestMethod]
public void EqualsComparedToNullTest()
{
    Assert.IsFalse(rhona.Equals(null));
}

And this works (before we change anything). Before we do write some code - let's examine the method signature of Equals. What type is the parameter? - it is object, not Employee. This means that we are going to need to check that our object is an Employee. Again a test for this - lets compare our Employee to a different type (in this case I'll use the EmployeeTest class)

[TestMethod]
public void EqualsComparedToAnotherType()
{
    Assert.IsFalse(rhona.Equals(new EmployeeTest()));
}

Now with that test working, lets finally write some code for Equals.

public override bool Equals(object obj)
{
    if (obj == null)
        return false;

    Employee other = obj as Employee;
    if (other == null)
        return false;

    if (this.employeeNumber == other.employeeNumber)
        return true;
    else
        return false;
}

Let's add another Equals method - this one will take an Employee as a parameter.

public bool Equals(Employee obj)
{
    if (obj == null)
        return false;

    if (this.employeeNumber == obj.employeeNumber)
        return true;
    else
        return false;
}

Wouldn't it be easier if this method is called - there is no casting here. And for all of the tests (bar the one we are comparing to a completely other type) we are comparing to an Employee. If you run the tests in the debugger and step through them you will see that the Assert.AreEquals(rhona, rhonda) calls the Equals with object as a parameter, but Assert.IsTrue(rhona.Equals(rhonda)) calls the Equals method with an Employee as parameter. The former calls a static method in object

public static bool Equals(object objA, object objB);

The later is calling our Equals method directly. But more on that later - it is useful! We need to look at how we might use our Employee (e.g. in Lists etc.)

So a few things to deal with, which will be in subsequent posts

• How to use this Equals method with Employee
• What to do with the GetHashCode() method

It's clear in Henley - The first Wallingford Head

The amount of people who on Sunday December 10th 1995 told me the "it's clear in Henley" was unbelievable. As you can probably guess it wasn't clear in Wallingford. More of that later....

You might want to read the first article on Getting Wallingford Head started before reading this one.

The year of 1995 was amazing. Rowing was still quite new to me - I had joined the Men's senior squad at Wallingford and was running and organising it with our head coach Richard Tinkler. We had a huge squad - at one time over forty people. At The Head of the River Race in March Wallingford had three entries coming in 39th, 113th and 238th (although I was disappointed not to make any of the crews). When the regatta season started we had a fairly difficult run in to Henley Royal Regatta in choosing our crews, including our top crew. We were one of the largest clubs at Henley that year with two eights in the Thames Cup, a Wyfold coxless four and a coxed four in Britannia (yes that is the correct spelling). Plus two of the vets qualified in pair in the Goblets. Being so close to Henley, we moved our training there during the week and then back to Wallingford for the weekend (when Henley got busy). It was such as good atmosphere there. The evening of the qualifying races was pure joy (maybe because I wasn't racing!) - especially when the results were announced and we got both crews into the Thames Cup. Unfortunately all our crews were knocked out in the first round with the exception of our coxed four in the Brit - which went on to win the event. Wallingford's first win in fifteen years. And they won in style, coming from behind. Superb.

Throughout the rest of the summer I cannot say my attention was drawn to the head race I had put in the rowing calendar. I kept rowing (including being in a winning eight at Peterborough Summer) and then went straight back into training.

Wallingford Sculls is the one of the first head races of the autumn and again I shadowed Roger Brown who was running the event. At the time I started to think about what boat classes we were offering - all the head races before Christmas were for small boats and fours. There were no events for eights. But at Wallingford we do all a lot of our training in eights - we have such a big squad it was necessary to do so. Also one of our target clubs to enter were Oxford colleges and schools - who all train in eights as well. Oxford Brookes (who had two eights stored at Wallingford) also trained full time in Eights.

So I changed the event to be fours and eights. I really don't like it when some people told me it wasn't possible to run an eights head - the river isn't suitable or we won't get the boats into the trailer park. Looking back I didn't really know if I could run this event, but at the time I was convinced that we could run an eights event and run a good one. I remember rowing outings racing side by side from the bottom lock to the club - intertwining blades a lot of the way (most rowers will have had at least one outing like that).

Then their was the issue of the course. I had thought that the sculling head course of 4 kilometre would be too short. We could extend it slightly (to 3 miles) which would make starting easier. I raced in Wallingford Sculls in a quad, which was actually useful to think about some of the logistics from the crew's point of view. For the sculling head we need the majority of crews well below the start (below a bridge and narrow part of river) with some of the boats able to turn into position easily (if you have raced at Wallingford then you will probably understand that better than I have explained). For Wallingford Head all crews need to be below the bridge first - and the timing and start teams need to do a bit more walking (I did get complaints about that!).

There was still a lot of work to do. I took on so much myself - it was my baby. Publicity was pre-internet - so I printed out lots of flyers and every event we competed I had the rowers who were competing put flyers on cars. On one night I remember typing into Excel all the rowing club addresses from the almanac and then doing a large post run. I left getting permission to almost the last minute - permission from South Oxfordshire District Council to use the car park and the Environment Agency (they were a lot tougher on conditions then -  including making sure that the lock keepers were informed with flyers to hand out to boats coming through - IN DECEMBER!).

I was also the entries secretary. Again pre-internet, so everything on paper (with cheques). I remember the entries arriving in the post and a few hand delivered. From many places - Hereford, Bristol, Stourport, Warwick, London. And from public schools - Radley, Latymer, Abingdon, Shiplake. I look at the entry list now and virtually all the clubs still enter each year.

We did offer pairs events as well - and did so for a few more years after 1995. Although, after a while we decided to drop them as they get in the way when putting in gaps between events. Normally the fastest pair is faster than the slowest girl's fours - so we end up having pairs overtake fours.

In total we got 99 entries - a very good start. It would cost around £350 to host the event (excluding the cost of pots which we could reuse). The Wallingford entries alone would cover that cost. All was looking good.

The day of the race came. The first division for the Sculling Head was midday - but it get's dark earlier in December. So the first division was at 11am. Meaning a very early start to get everyone in place - the rafts were moved at 7am along with people out on the river putting out buoys and signs. Registration started at 7am as well - and I can tell you it is bl***y cold in December.

At that time in the morning we had a problem. You couldn't see to the other side of the river. We had freezing fog. Usually it will burn off - first crews will be boating around 10am, so we could have a few hours. I wasn't worrying about this. Everything was in place - rafts, buoys, catering, safety cover. Crews had arrived - we had got the trailers into the car park without a problem (although I do remember an incident a few years later when I think Upper Thames took a piece off the end of a boat around a tree when leaving!).

But we had this freezing fog. At 9am it wasn't looking good - if anything it was looking heavier. As people arrived they kept telling me that it was clear in Henley - so I shouldn't worry. At least everyone could see what the problem was. I have rowed in freezing fog and it's not normally a big problem. You can see sufficiently in front of yourself to manage. But in a race other crews are going to be so much closer. And this fog was heavy. I wouldn't have rowed in this even if not racing.

Boating at 10am was delayed. At the time we were quite inexperienced about putting in place a contingency plan. The second division wasn't due until 1.30pm, with boating starting at 12.30. But their wasn't a lot of time afterwards to delay this division. However, there was a bit of time between divisions. Quickly thinking about the problem meant that the latest that the last crew could finish would be around 12.15 - 20 minutes of racing meaning latest boating time of 11am. Not much margin - another hour we could probably allow. So we got the message out.

11am came - and went. Next change was to allow crews to decided what they wanted to race in a single division with boating starting at 1pm. We would just about finish before it gets dark. At the time their had been complaints amongst crews who had raced in Bristol a few weeks before finishing on the dark - we didn't want to start getting a reputation for such things.

1pm came - and there was no choice but to cancel.

I had entered a few head races - and I was annoyed when one was cancelled the event wouldn't refund entry fees. I had decided up front that we would guarantee the entry fees if we cancelled the event. And we have done this ever since - including for the regatta (which has a lot more costs). It doesn't cost a lot for a club to host an event like this. It does if we take into account £2500 for prizes - but unless you are really silly and have the year engraved on them, you will get to re-use them for the next event. (Incidentally, we had our prizes engraved with WALLINGFORD HEAD. Winners of recent Wallingford events might notice that the pots say WALLINGFORD ROWING CLUB. Otherwise we could end up with several thousand pounds of prizes stored).

I think everyone who had entered took the cancellation on the chin. It was so disappointing though. I had put in such a large amount of work but their was nothing we could do. All the volunteers had got behind the event - the club was I think was behind it. The volunteers got the rafts back to the club and collected the buoys. We had I think sold a large amount of tea and cake - although not enough to cover the costs.

If I look at the briefing sheet for the volunteers I see that the team leaders are mostly the same people that are my best friends now. And some are still involved in the events.

After all the clear up was done - and I took a whole load of stuff home, it was time for the pub. I have to say that those close friends all gave me a cheer, which was very much appreciated. And - there was still freezing fog the next morning. And a bit of a hangover.

The race in 1995 was a bit of a practice for getting everything in place and knowing what to do. A good learning experience. The event in 1996 attracted 97 entries - mainly the same clubs who had came the year before. I was supposed to race in 1995 (although I can't see where I would have had time) - but in 1996 I was awaiting a hernia operation, so I have never got to race in this event. Entries since 1996 have increased - in 1997 we received 171 entries - and last years event had a self imposed limit of 260. The race in 1997 became a lot more serious with the large number of competitors (we estimate over 1000 in that year). Safety became a much more important concern. Although their were no incidents, we were reliant on club members driving launches or being on the bank. We did have one on-river rescue service. But we would need to take it much more seriously.

For note: of the 18 years since the first event in 1995, there have been six cancellations (1995, 2000, 2006, 2007, 2009 and 2012) and the 2002 race had a reduced entry due to the stream.

Getting Wallingford Head started

It's that time of year when rowers have just started winter training, with the racing part of the training consisting of time trials – known as Head of the River races. I thought that I'd like a few posts about how Wallingford Head got started.

Winter training normally starts well – you actually look forward to a winter of training after either a good or bad summer of racing with optimism that this will be your year! Racing during the winter consists of Head of the River races – time trials where crews start at 10 to 15 second gaps and race of distances around 3 or 4 miles. The culmination of the winter racing season comes with the Head of the River race on the Tideway in London, where 400 crew race in the Men's' Head and over 300 in the Women's Head over the "Championship Course" (the same course as the Boat Race but from Chiswick to Putney).

At Wallingford we have what I regard is the best piece of river on the Thames. It is the longest stretch (between locks) upstream of Teddington. This meant rowing outings were on average usually 16k from the club to Cleeve lock (or 20k lock to lock).

On the river we were running one Head Race – Wallingford Long Distance Sculls, which started in the early 1970's. It was for sculling boats and run over a 4km course. It had a good healthy entry (although in 1995 it was less that half of what it is now), but with the entry all being small boats it was run to as a "service" to rowing rather than to help provide some fund raising income to the club.

I had joined Wallingford Rowing Club in 1993 competing in a "pub" regatta. I had not long returned from working in Indonesia and entered this event – not winning my first race – so spent the rest of the day having a few beers. But I was fairly hooked on rowing (which became my focus for several years). After the pub regatta I joined a beginner's squad for a 12 week course. At the end of the course in early December a time trial was held .This was my first real race – only around 3k, but lots of fun. From then on I ended up training and racing in a novice squad and having a really good time. And I ended up running the novice squad!

In 1994 Wallingford Rowing Club was attempting to transform itself. It had taken on debt via a debenture scheme to buy new boats and blades and adult squad rowing at the club was becoming very healthy. I was now running the senior men's squad – which was numbering over 30 people with the introduction of professional coaching.

The debenture scheme did indeed transform the club – new boats and blades and lots of people rowing. But the large number of people rowing meant further strains on equipment. But with no more money to pay for any new equipment – we were also paying for a professional coach.

At the end of the year another time trial was held at the club with entries from all the club squads as well as Oxford Brookes (who at time rented two racks at the club) and some Oxford colleges.

After that event I was thinking why we weren't running a proper head race – rather than one for 20 or so crews. I had just helped at my first Wallingford Sculls and was pretty driven at that time. So (on 25th November 1994) I faxed the Amateur Rowing Association (now British Rowing) to ask for the event to be put into the calendar. The contents of the fax were simply:

"We wish to hold a small boats head (Wallingford Small Boats Head) next year on Sunday December 10th, 1995. Could you please add this to the list of regattas/heads to be passed at the ARA council meeting on the 27th. If you require any further information please do not hesitate to contact me at work on the above number, at home on XXXX XXXXXX or by email at XX@XX.co.uk. Alternatively contact Pete Sudbury, the club captain on XXXX XXXXXX."

Amazingly we were in the calendar after the ARA council meeting.

I'm not sure how I got the club to agree to do this – pretty sure it involved a conversation with the club captain at the time (Pete Sudbury) who probably said great idea and go ahead. Nor did I know what I had let myself in for.

You will note that the intention was to run a small boats head – the idea was to accept entries in sweep oars boat (to complement the sculling head) but not eights!

Next – the first Wallingford Head

C#: IEnumerable

I've been asked a few times by my apprentice groups to explain IEnumerable. Something that in C# we use every day. IEnumerable is an interface, which when implemented supports iteration. There are two interfaces - the non-generic one (for looping over non-generic collections) and the generic one.

Firstly, lets look at the definition of the interfaces.

namespace System.Collections
{
   public interface IEnumerable
   {
      IEnumerator GetEnumerator();
   }
}

namespace System.Collections.Generic
{
   public interface IEnumerable<out T> : IEnumerable
   {
      IEnumerator<T> GetEnumerator();
   }
}

Now if we have a Listand look at the first line of the definition we will see that it implements the interfaces.

public class List<T> : IList<T>, ICollection<T>, IEnumerable<T>, 
                       IList, ICollection, IEnumerable

Let's create an example to play with. We will have a class (called Place) with some fields.

public class Place
{
    public string PlaceName { get; set; }
    public string GaelicName { get; set; }
    public int Population { get; set; }

    public override string ToString()
    {
        return String.Format("Place {0} ({1}) pop {2}",
            PlaceName, GaelicName, Population);
    }
}

And in a console program let us populate an instance of a List of Place (List<Place>).

List<Place> places = new List 
{
    new Place { PlaceName = "Lewis and Harris", GaelicName = "Leòdhas agus na Hearadh", Population = 21031 },
    new Place { PlaceName = "South Uist", GaelicName = "Uibhist a Deas", Population = 1754 },
    new Place { PlaceName = "North Uist", GaelicName = "Uibhist a Tuath", Population = 1254 },
    new Place { PlaceName = "Benbecula", GaelicName = "Beinn nam Fadhla", Population = 1303 },
    new Place { PlaceName = "Barra", GaelicName = "Barraigh", Population = 1174 },
    new Place { PlaceName = "Scalpay", GaelicName = "Sgalpaigh", Population = 291 },
    new Place { PlaceName = "Great Bernera", GaelicName = "Beàrnaraigh Mòr", Population = 252 },
    new Place { PlaceName = "Grimsay", GaelicName = "Griomasaigh", Population = 169 },
    new Place { PlaceName = "Berneray", GaelicName = "Beàrnaraigh", Population = 138 },
    new Place { PlaceName = "Eriskay", GaelicName = "Beàrnaraigh", Population = 143 },
    new Place { PlaceName = "Vatersay", GaelicName = "Bhatarsaigh", Population = 90 },
    new Place { PlaceName = "Baleshare", GaelicName = "Baile Sear", Population = 58 }
};

If we want to output them

foreach (Place place in places)
    Console.WriteLine(place);

And when you run this it will use the ToString() method

So what is exactly happening. Let's look at this code

var enumerator = places.GetEnumerator();

while (a.MoveNext())
{
    Place p = enumerator.Current;
    Console.WriteLine("Place {0}", p);
}

Here we are calling the GetEnumerator() method. Out loop is then checking that we can move to the next element - this MoveNext method will return true if there are (more) elements to process. If there are we can get the current element with the Current method, which we can then print out. When we did our original loop - this is essentially what is happening. The foreach statement in C# will hide this complexity. But foreach will work with classes that implement IEnumerable.

So let's extend our example to add another class (IslandGroup) which we will use to encapsulate details about a group of islands - in this case the list of islands above are the Outer Hebrides. So let's create a class for this, with some properties including a Dictionary containing the islands and one to return the total population. Apologies for the lack of comments - my apprentice groups would crucify me!

public class IslandGroup : IEnumerable<place>
{
    public string IslandGroupName { get; private set; }
    public Dictionary<string,Place> Islands { get; private set; }

    public IslandGroup(string islandGroupName)
    {
        this.IslandGroupName = islandGroupName;
        Islands = new Dictionary<string,Place>();
    }

    public void AddIsland(Place island)
    {
        // Add the island if it isn't in the Islands already
        if (!Islands.ContainsKey(island.PlaceName))
            Islands.Add(island.PlaceName, island);
    }

    public int TotalPopulation
    {
        get
        {
            return Islands.Sum(v => v.Value.Population);
        }
    }
}

We can use this class and populate the dictionary as well as returning the total population of all the islands with something like this

IslandGroup outerHebrides = new IslandGroup("Outer Hebredies");

// Add each island to the class
foreach (var place in places)
    outerHebrides.AddIsland(place);

Console.WriteLine("Population {0}", outerHebrides.TotalPopulation);

Now right click on IEnumerable<Place> on the definition of the class - choose Implement Interface. This will create two methods as below

public IEnumerator<Place> GetEnumerator()
{
    throw new NotImplementedException();
}

System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
{
    throw new NotImplementedException();
}

Why two methods - well look at the definition of IEnumerable<T> which implements IEnumerable. So you need both. In the end we will make one method (the non-generic GetEnumerator()) call the other.

Have you heard of the yield keyword. The yield keyword is user an iterator method to give control back to the loop. So when you do a foreach loop a method gets called to perform the iteration. In this method we will put in a yield statement. So here is some code

public IEnumerator<Place> GetEnumerator()
{
    foreach (var place in Islands)
        yield return place.Value;
}

Our class encapsulates data for the islands which we store in a Dictionary. In the loop we want to return each place - hence in our loop we are using the .Value property and returning this. We are using yield return <expression>, which every time this gets called the expression will be returned. In our code to execute this we will use the instance of the class

foreach (var item in outerHebrides)
    Console.WriteLine(item);

But are you asking - why don't we just loop through the Dictionary property using something like this

foreach (var item in outerHebrides.Islands)
    Console.WriteLine(item.Value);

It just depends on what data you want to make available and what functionality you want to return. Let's say we want to return (in this case) the islands from lowest population first. Since we have written our own iterator we can do this.

public IEnumerator<Place> GetEnumerator()
{
    var inOrder = from i in Islands.Values
                  orderby i.Population ascending
                  select i;

    foreach (var place in inOrder)
        yield return place;
}

Now when we run this the output will be in the order we determine

Finally, you don't need to implement the interface IEnumerable<T> - you can have methods returning that type. So you could write a method like this

public IEnumerable<Place> GaelicAlphabeticalOrder()
{
    var inOrder = from i in Islands.Values
                  orderby i.GaelicName ascending
                  select i;

    foreach (var place in inOrder)
        yield return place;
}

Which could be executed with

foreach (var place in places)
    outerHebrides.AddIsland(place);

Thus we don't need to implement this at a class level - or if we do, we can provide alternative methods (and these methods could take parameters etc.)

The yield keyword can be used as above, but also as

yield break;

Which will end the iteration. You can also use the yield keyword in static methods, for example

public static IEnumerable<string> ScottishIslandGroups()
{
    yield return "Outer Hebrides";
    yield return "Inner Hebrides";
    yield return "Shetland";
    yield return "Orkney";
    yield return "Islands of the Clyde";
    yield return "Islands of the Forth";
}

Or as a property

public static IEnumerable<string> WelshIslandGroups
{
    get
    {
        yield return "Anglesey";
        yield return "Bristol Channel";
        yield return "Ceredigion";
        yield return "Gower";
        yield return "Gwynedd";
        yield return "Pembrokeshire";
        yield return "St Tudwal's Islands";
        yield return "Vale of Glamorgan";
    }
}

Used as

foreach (string islandGroup in IslandGroup.WelshIslandGroups)
    Console.WriteLine(islandGroup);