Using Composite Keys

Composite keys are similar to natural keys in many ways, except that a composite key comprises multiple database columns, and must therefore be mapped as a value object.

Composite Key Types

Every entity in LightSpeed has an Id property.  In the case of a composite key, the Id property has to contain multiple values.  This happens by making the identity type a composite type – a struct (.NET value type) with a field for each column of the composite key.

The columns of the composite key will be mapped to the fields of this identity type.  To refer to composite key columns, therefore, you must refer to entity.Id.field_name; the columns are members of the composite Id, not distinct properties on the entity.

Composite Keys in the Designer

When you drag a table with a composite key onto the designer, LightSpeed infers the key columns from the database primary key, and displays identity fields for the key columns.

The identity type is automatically generated.  Its name is the entity name followed by Id.  For example, an entity named InternationalProduct will have an identity type named InternationalProductId.

You can create composite keys in a model-first approach, by using the LightSpeed Model Explorer to add identity properties to an entity.  You should not normally do this: if you are defining new entities rather than mapping existing tables, you should use a surrogate key instead.

Composite Keys in Hand‑Coded Entities

If you are hand‑coding an entity with a composite key, you must provide an identity type, either by locating an existing type that fits the composite key columns or (more usually) by coding a specific identity type.  The identity type must be a struct (value type) and should be immutable (all fields marked readonly).

public struct InternationalProductId
{
  private readonly int _productId;
  private readonly string _languageId;
  public InternationalProductId(int productId, string languageId)
  {
    _productId = productId;
    _languageId = languageId;
  }
  public int ProductId
  {
    get { return _productId; }
  }
  public string LanguageId
  {
    get { return _languageId; }
  }
  // Overrides of Equals and GetHashCode omitted for clarity.
  // You should always provide these methods as they can markedly
  // affect efficiency.
}

You can then specify this type as the TId type parameter when deriving from Entity<TId>:

public class InternationalProduct : Entity<InternationalProductId>

Assigning Composite Keys

Composite keys are assigned in the same way as other natural keys: by overriding the GeneratedId method.  Similar considerations apply in terms of storing Id generation data in transient fields and ensuring the Id generation data is available before the entity joins a unit of work.  The GeneratedId override must return an instance of the composite key type.

[Transient]
private InternationalProductId _pendingId;
public InternationalProduct(int productId, string languageId)
{
  _pendingId = new InternationalProductId(productId, languageId);
}
protected override object GeneratedId()
{
  return _pendingId;
}

Composite Foreign Keys

When another table has a foreign key to a composite-keyed table, the foreign key field must be of the composite key type, the foreign key columns must be mapped to the fields of the composite key type, and the foreign key field must be marked as a value object.

public class Advert : Entity<int>
{
  [ValueObject]
  [ValueObjectColumn("ProductId", "PRD_ID")]
  [ValueObjectColumn("LanguageId", "LANG_ID")]
  private InternationalProductId _productId;
  public InternationalProductId ProductId
  {
    get { return _productId; }
    set { Set(ref _productId, value, "ProductId"); }
  }
  private readonly EntityHolder<InternationalProduct> _product = new EntityHolder<InternationalProduct>();
  public InternationalProduct Product
  {
    get { return Get(_product); }
    set { Set(_product, value); }
  }
}

If you are using the designer, it can generate this code for you, but it may not be able to infer the column mappings from the database.  You may therefore need to manually set up the association and map columns.

Foreign Keys That Are Part of a Composite Key

In some cases, one of the columns of a composite key may also be the foreign key for an association.  For example, in the InternationalProduct example, InternationalProduct.Id.ProductId might be the foreign key for an association to a Product entity.  This runs counter to the LightSpeed convention that an association’s foreign key is represented by a field with the name association_nameId.

To handle this case, select the association arrow and enter the path to the key field in the Key Property Reference box.  (In code, apply ForeignKeyFieldAttribute to the EntityHolder.)  In the example above, the path would be Id.ProductId.

Composite Foreign Keys That Overlap the Primary Key

The Key Property Reference technique works when you have a simple foreign key which happens to be a column in the composite primary key.  In some cases, you will have a composite foreign key, some of whose fields are columns in the primary key.  In this case, you cannot use Key Property Reference, because there is no single field which acts as the foreign key; nor can you provide a composite foreign key field, because that would require the overlapping columns to be mapped twice.  Instead, you must provide a custom association resolver.  This is a type which implements IAssociationResolver and returns a QueryExpression describing how to look up an associated entity or collection.

Using a custom resolver brings certain limitations particularly around eager loading and cascade delete.  It is strongly recommended that you try to modify your database schema to avoid the use of overlapping foreign and primary composite keys.  If using a custom resolver is unavoidable, contact Mindscape technical support for a sample.

Many‑to‑Many Associations Represented As Composite Keys

A common idiom for many‑to‑many associations, even in databases that otherwise use surrogate keys, is to omit the surrogate key on the through table.  That is, the through table contains foreign keys to the tables being joined, and its primary key is a composite key on these two columns rather than a separate Id column.  For information about implementing this in LightSpeed, see the online article Many-to-many associations and composite keys in LightSpeed.