Chapter 2. Composite Primary Keys
Prev   Next

Chapter 2. Composite Primary Keys

Often times you'll come across a database with multiple primary keys - especially in legacy systems. This is known as composite keys. Although modern conventions discourage the use of multiple primary keys - preferring instead the use of an autoincrement column - they still can surface as a requirement.

For example, let's assume that we track orders by the product, customer id and purchase date - not by an order number. To retrieve a specific order, an example sql query might look like this:

SELECT * FROM Orders WHERE customerId=123 AND productId=65 AND purchaseDate='09/13/2006'

Elementary can handle this with minimal effort. You only need to do two steps:

  1. Update your database schema to reflect the multiple primary keys. Elementary will automatically detect which columns are primary keys.

  2. Use either the ColumnCompositeKey or PropertyCompositeKey to retrieve your object.

    The ColumnCompositeKey or PropertyCompositeKey objects are similar to Hashtables and assign a name value pair, which is then processed by Elementary to generate the correct SQL statements.

    Below are examples using either approach.

    // ColumnCompositeKey uses the column names for lookup
IObjectMapper<Order> m = Elementary.GetIObjectMapper<Order>();

ColumnCompositeKey cck = new ColumnCompositeKey();
cck["customerId"] = 123;
cck["productId"] = 65;
cck["purchaseDate"] = DateTime.Today;

Order order = m.GetObject(cck);
    // PropertyCompositeKey uses the property names and
// maps them back to the column names for lookup
// This is useful when your column names may change, but your
// property names won't, since it doesn't require any recompiles
// when schema changes occur
IObjectMapper<Order> m = Elementary.GetIObjectMapper<Order>();

PropertyCompositeKey pck = new PropertyCompositeKey();
cck["CustomerId"] = 123;
cck["ProductId"] = 65;
cck["PurchaseDate"] = DateTime.Today;

Order order = m.GetObject(cck);

    In general, it's recommended that you use PropertyCompositeKey since it removes schema specific mappings from your code and allow you to focus instead on true OOP approaches.

Composite key capabilities work with all advanced Elementary options, to include child mappings, filters, aliases, etc.

Prev   Next
3. A Simple Example - Explained Home Chapter 3. Child Mappings (1-1, 1-n, n-n)