onsdag den 3. juli 2013

Linq-to-sql enums support


I don't much like it when I must specify hard-coded values into my code. It can be a debugging nightmare, furthermore it looks ghastly. Microsoft's Sql Server OR-mapper linq-to-sql, which I like very much, unfortuantely do not have built-in designer support for generating enums from lookup-tables, which has previously found me doing that hard-coding I do not like to do. Well, no more - look on.

Consider the examples database-tables:


The 'periodStatusId' field is the one that I would ideally want to fill in as ...


myObject.periodStatusId = periodStatusIdEnum.OpenedStatus


... as opposed to ...


myObject.periodStatusId = databaseContext.PeriodStatus.Single( foo => foo.Description.Equals("Opened"));


Well there's a will and a way. What we can do is pre-define an enum and alter our database-context to reflect it. So, in your database-layer, define an enum and copy its values from the database. Like this:


public enum PeriodStatusEnum
{
ClosedByUser = 1,
ClosedBySystem = 2,
ReOpenedByUser = 3
}


Above, the integer corresponds to the auto-incrementing interger id of the lookup-table, the text corresponds to the description field.

It, well, it sucks, to have to pre-define the enumeration. Much preferred would be to do this in the designer, but that's not possible with linq-to-sql. So pre-define it we do, in the know that at least this will be a few hundred percent better than going hard-code style.

Now the enum has been created, we'll refer to this instead of the database's native type in the datacontext. Change both the lookup-table's and the referencing table's field types to your enum, pre-fixed with a 'global::' value, i.e. global::yourNamespace(s).periodStatusEnum. Like so:


That's it - now you can use your enumeration as opposed to actively looking up the value. Like this:


TidsRegPeriode period = new TidsRegPeriode();

// populate field by referencing your enumeration
period.periodeStatusId = timeRegistrering.Domain.PeriodStatusEnum.ReOpenedByUser;

... other fields populated...

dbContext.TidsRegPeriodes.InsertOnSubmit(period);
dbContext.SubmitChanges();


Again, it would be tremendously better if we could specify enums in the designer. But we can't - so this is a heck of a lot better.