Freigeben über

Fantasy Soccer - part 3 - Unit of Measure

  • Artikel

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.

Comments

  • Anonymous
    May 02, 2008
    Actually, the DBA would be wrong if they put data with different units into the same column without converting to a common system.Its almost like saying water boils at 100 and freezes at 273, without telling first one is Celsius and second is Kelvin.
  • Anonymous
    May 03, 2008
    TanveerI hear what you are saying. More often than not the DBA would ensure the data was first converted to LBS or Kilos.Still some platform type applications, solve this problem a level further up, so that they are more general solutions etc.I know I'm not making a very convincing argument, probably because I personally wouldn't do it, all I know is I've seen it before, and as such I wanted to talk about a way of modeling it.Remember nothing is quite as black and white as you are making out.Alex
  • Anonymous
    May 05, 2008
    @tanveeractually I like the the formula, and keeping this out of the database and into a linq query is very appealing. (or at least also available from linq)@alexCan you drive a complex property from the db or would that just be an entity then? I guess what I am missing is how the complex type is populated in this example.As for-ScaledWeight = athlete.Weight.Scale * athlete.Weight.ValueIf it was an entity, and the weight was stored in the db, would you just put an instance variable on athlete vs the static property which projects the new type? What does the ScaledAthlete type get you?then:from a in Athlete  orderby a.ScaledWeight descending   select a.Athlete;
  • Anonymous
    May 15, 2008
    Had a chance to talk with Danny a bit about the value of complex types and the direction you guys are going with them. Have a much better understanding now. (plus re-read the MSDN & EF FAQ)As an alternative to the ScaledAthlete class which works on top of the <ComplexProperty> of Athlete, I think another solution might be to use the <ComplexType> and map the type to some weight fields on an entity.<ComplexType Name="UnitOfMeasure"> <Property Name="Value" Type="Decimal"/> <Property Name="Scale" Type="Decimal"/> <Property Name="Units" Type="String"/></ComplexType><EntityType Name="Athelte"> <Key>   <PropertyRef Name="AthleteId" /> </Key> <Property Name="Weight" Type="Self.UnitOfMeasure"  Nullable="false"/></EntityType>Then you get the value object in the domain that you can re-use accross types, e.g. calculated properties, sorting, comparing & converting weights, without having to delcare a new 'wrapper' type in the context for each new type that may have weight. For some reason the ScaledAthlete bothers me :)Does that fit?(I may have wandered a bit outside the point of your post, but it was helpful as it lead me to give it a try and gain some insight, so thanks!)