Today I spent a bit of time working on the data model for my fantasy soccer application.

I don't want to talk about the actual model today though, because I'm not really ready.

What I want to do is talk about a problem that occurred to me, namely Unit Of Measure. 

Any real sport fan knows you need to know what kind of shape someone is in, did they put on too much weight in the off season? In cycling this stuff is deadly serious, Jan Ullrich was famous for coming back from winter, carrying "too much" weight, in fact so much so that it often took Jan until the 2nd to 3rd week of the Tour De France to get into optimal condition.

Anyway the point of all this is that weight has units, and if I tell you someone's weight is 120, you are none the wiser until I tell you the units (pounds or kilos).

Now if you have a database being populated from information gathered around the world you almost certainly need to deal with this sort of problem. In NZ you get Kilos, in the US pounds etc etc.

If you don't know the units in a particular row, you can't do meaningful comparisons, i.e. something like this makes no sense:

var heaviest = from athlete in ctx.Athletes
               orderby athlete.Weight descending
               select athlete;

Well ComplexTypes are good for this sort of thing i.e.

<ComplexProperty Name="UnitOfMeasure">
    <Property Name="Value" Type="Decimal"/>
    <Property Name="Scale" Type="Decimal"/>
    <Property Name="Units" Type="String"/>
</ComplexProperty>

If you now define the athletes weight to be a UnitOfMeasure you know both the Weight and Units:

i.e. if you are operating in Kilos you say something like this:

athlete.Weight.Value = 90;
athlete.Weight.Scale = 2.2M;
athlete.Weight.Units = "kg";

Which is equivalent to this:

athlete.Weight.Value = 198;
athlete.Weight.Scale = 1;
athlete.Weight.Units = "lbs";

Because there are 2.2 lbs / kilo.

At this point the we can reason about the athlete' weight:

foreach (Athlete a in ctx.Athletes)
   
Console.WriteLine("{0} weighs {1} {2}",
                       a.Firstname,
                       a.Weight.Value,
                       a.Weight.Units);

Unfortunately sorting is still not possible.

However if you add a helper class like this:

public class ScaledAthlete
{
    public Athlete Athlete;
    public Decimal ScaledWeight;
}

And add this property to your partial ObjectContext class:

public static IQueryable<ScaledAthlete> ScaledAthletes{
    get{
        return from athlete in this.Athletes
               select new ScaledAthlete{
                   Athlete = athlete, 
                   ScaledWeight = athlete.Weight.Scale * athlete.Weight.Value};
    }
}

Then you can write code like this:

var heaviest = from a in Athlete.ScaledAthletes
               orderby a.ScaledWeight descending
               select a.Athlete;

foreach (Athlete a in heaviest)
    Console.WriteLine("{0} weighs {1} {2}",
                      a.Firstname,
                      a.Weight.Value,
                      a.Weight.Units);

And it will work nicely, with all the real work happening in the database.

Some key points:

  1. We need to use an expression to for ScaledWeight that can be converted to a database expression: i.e. ScaledWeight = athlete.Weight.Scale * athlete.Weight.Value is simple enough to be converted to T-SQL.
  2. We need the ScaledAthlete class because otherwise it would be an anonymous type, which of course you can't use in a method signature (i.e. IQueryable<var> is not valid).
  3. We project the actual Athlete entity into the Athlete field so that if we actually want the athlete it is available and more importantly it is Attached to the underlying ObjectContext, meaning of course we can make and save changes as required.

The thing that is nice about this approach is the scaling is built-in to the datasource (i.e. ScaledAthletes), so it is just there whenever you issue a query over ScaledAthletes.

At this point you might be surprise to find out there is a much easier way too.

You can re-write this using a let like this:

return from athlete in ctx.Athletes 
       let weight = athlete.Weight.Value * athlete.Weight.Scale
       orderby weight descending
       select athlete;

Or my even without the let:

return from athlete in ctx.Athletes 
       orderby athlete.Weight.Value * athlete.Weight.Scale descending
       select athlete;

Notice you don't need a helper class, a helper method, and you can have more control over the resulting query.

So why the big detour... well as I said in my statement of intent this is supposed to be warts and all, what you just saw is how I got there and what I learnt along the way.

Happy measuring.