Home » Blog

Associations and composite keys

Tagged as LightSpeed

In previous articles, we’ve seen how to create and query for entities with composite keys in LightSpeed 3. But entities rarely exist in isolation: they have associations to other entities. In this article, we’ll see what how to set up associations to entities when composite keys are involved.

Refresher – association patterns in LightSpeed

As a reminder, a one-to-many LightSpeed association has two ends. At the parent end, there’ll be an EntityCollection of children. At the child end there’ll be an EntityHolder for its parent, and a scalar property for the foreign key to its parent (the parent Id). (In the designer, we hide the foreign key property, but it’s there all the same.)

One-to-one associations are similar, except they have an EntityHolder at both ends. Again, there’s a foreign key only at one end.

The association pattern and composite keys

The EntityCollection and EntityHolder classes are completely agnostic about the type of the foreign key. So the only thing we need to look at is the foreign key (parent ID). This has the be the same type as the Id of the entity it’s associating to. Well, in the case of a composite key, there’s a value type defined for that. So we just use that type for the foreign key field. But because this one field is going to be mapped to multiple columns in the database, we have to remember to specify ValueObjectAttribute on it. This tells LightSpeed to store each member of the structure in a separate column.

Here’s an example, where Product has a one-to-many association to Advert (omitting wrapper properties):

public class Product : Entity<ProductId>
{
  private readonly EntityCollection<Advert> _adverts = new EntityCollection<Advert>();
}
 
public class Advert : Entity<int>
{
  [ValueObject]
  private ProductId _productId;  // foreign key - note type and ValueObject attribute
  private readonly EntityHolder<Product> _product = new EntityHolder<Product>();
}

So associations with composite foreign keys follow the normal association pattern exactly, using the composite key struct as the foreign key type.

Database column mapping and associations

However, there’s a wrinkle. LightSpeed has the convention that a field in a value object field maps to a column whose name is the value object name followed by the field name. So, in the above example, in the Advert table, LightSpeed would expect to see columns named ProductIdProductTypeId and ProductIdCultureId. That’s pretty bad, but given that the whole reason for composite keys is that you’ve got a legacy database you can’t change, the chances of that legacy database having luckily adopted this naming convention are slightly lower than those of being run over by a train in your bathroom while you’re pretending to be an artichoke.

To customise the field mappings, you’d normally use ColumnAttribute, but you can’t in this case because there are multiple columns that need mapping. (When you apply ColumnAttribute to a value object, it modifies the prefix, but the individual fields keep the same names.) Instead, in this case we use the new ValueObjectColumnAttribute. This is specific to value objects and allows you to override each column mapping individually.

Let’s suppose that in the Advert table the product type ID column is called PTID and the culture ID column is called LANG_ID. (Don’t complain to me. It’s a legacy database.) Then we’d map our foreign key field like this:

public class Advert : Entity<int>
{
  [ValueObject]
  [ValueObjectColumn("ProductTypeId", "PTID")]
  [ValueObjectColumn("CultureId", "LANG_ID")]
  private ProductId _productId;
}

We’re done

And that’s it for composite keys. In this series, you’ve seen how to define entities with composite keys, how to query on the key, and how to define associations where the foreign key is composite. If you’ve got any more questions, or any feature requests around this, please post in the forum and let us know!

Add your comments
Posted by Ivan Towlson on 6 January 2010

Leave a Reply