For long lost friends and stalkers

ID: Type-safety in database code

I present here a wee collection of classes (‘library’ is too grand a word) for passing around database identifiers in a type-safe way in C# (and potentially Java and Scala too).

Background

In a typical database schema—at least most of the ones I’ve been involved with—most of your entities (relations) have a single integer primary key called, often, ‘ID’ or ‘Id’.

(Some tables have composite keys; some might use natural keys, like ‘ProductCode’ or ‘ISBN’; but in most cases it’s a single, integer key.)

Your application code, when referring to entities, can pass around entity objects (like instances of a ‘Customer’ class), or it can pass around ints representing the id.

It’s not always easy to make the decision what to pass, since for any given database-bothering method, some client code may have an entity object ready to pass in, and others might just have the ID. If the first thing the method does when passed an ID is to look up the record and create an entity object, it would save a database call to have the entity passed directly. However, some client code may not have an entity object to hand, and it’s an imposition to ask that they look it up themselves.

The second issue is that passing database IDs as integers is error-prone. Particularly if a method has more than one ID parameter:

It’s a little more verbose, but the win is that the compiler is able to catch many more silly mistakes than otherwise. The other advantage is that, in the example above, the return type is nicely self-documenting.

And because it’s a struct rather than a class, it’s exactly as efficient as passing a naked int.

And because it converts silently to and from integers, you can start using it in existing code, then gradually migrate the code which uses ints to using IDs.

Passing an ID or an entity object

What about the first problem we raised, about passing either an ID or an entity object, and the called method being able to use the entity object if it exists, or look it up from the ID?

That tricky ‘where’ clause ensures that the EntityType we’re talking about implements the interface too, i.e., it provides an Id property.

This interface has a slightly unwieldy name, but then when you see it as the parameter type of a method, it’s making quite a sophisticated promise: I accept an ID or an entity object, and if you pass in an ID, I’ll look it up, otherwise I’ll just use the entity you provide.

To convert the other way, from an IEntityOrID to the entity class, we may need to do a database lookup (or something), so the code for that would be specific to your application (and each entity class). However, it might look a little like this:

Entity Classes

GIven the scheme above, each entity class, e.g., Customer, must be defined like this:

public class Customer : IEntityOrID<Customer>
{
…

…and it needs to declare a public integer property with a getter called ‘Id’.

Convenience conversions

Passing an entity object to a method which expects an IEntityOrID will transparently work, since your entity implements the interface IEntityOrID<EntityType>. However, we can add another convenience conversion to ID:

That way we can pass an entity object into any method which expects an ID (of the correct type).

You’re not really supposed to create implicit conversions which lose information like this, but it’s not quite in the same class of transgressions as silently converting a double to an int since we’re not distorting the value; we’re taking a well-defined part of it (the ID).

Working with it

This is a relatively new (to me) technique, but it seems to be working quite well.

One tweak I need to make in my database access layer is something which turns IDs into ints when sending to the database.

The various implicit conversions make it quite a painless approach to adopt into an existing codebase.

The other thing I found is that supports the idea of having multiple entity classes with the same primary key type, for example, entity classes which represent different views onto the same database object. Even though each might provide a different set of information associated with an Order (say), all the classes can be passed in to methods which expect a reference to an Order record, since they all implement IEntityOrID<Order>.

Extension to other languages

Java does not have such a thing as a struct type, so you could do the same thing, but it would be rather less memory-efficient than just using ‘int’.

Scala 2.10 recently introduced value types, which would be perfectly suited to this technique. If I use it for any of my Scala projects, I’ll be a good blogger and write up my experiences.

Getting the code

I have not yet extracted code from the project which is using it, but I will bung it up on github at some point. I promise. Really it’s not much more than what I’ve written above, though, so if you’re interested in using it, go to town!