Identity Generation

Every LightSpeed entity has an Id, which LightSpeed uses to uniquely identify the entity.  When a newly created entity becomes part of a unit of work for the first time, LightSpeed assigns it an Id.  LightSpeed has a number of ways of generating Ids for entities.  The default identity method is KeyTable, which is an efficient and portable way of generating numeric Ids.  This section describes how to override the default, and the alternative options.

Identity Methods in LightSpeed

The available methods of generating Ids are defined in the IdentityMethod enumeration.  LightSpeed supports the following methods of generating Ids:

Specific methods are discussed in more detail below.

Setting the Identity Method Globally

You will normally choose a single method of generating Id values, which is used for all entities in your model.  This simplifies administration by applying a single consistent policy across the entire database.  To do this, set LightSpeedContext.IdentityMethod in code, or the identityMethod attribute in configuration.

<add name="Test"
     identityMethod="Guid" />

_context.IdentityMethod = IdentityMethod.Guid;

Overriding the Identity Method on a Per-Entity Basis

In a few cases, notably legacy databases where different tables have different identity types – for example, some tables have GUID primary keys while others have numeric primary keys – you may need to override the global identity method for specific tables.  To do this, select the entity that is mapped to that table, and set its Identity Method option.  (For hand-coded entities, apply TableAttribute and set the IdentityMethod property.)

Some legacy databases require that the identity be part of the business data rather than an opaque value.  For information about this, see Using Natural Keys in the Working with Legacy Databases chapter.

KeyTable Identity Generation

KeyTable identity generation uses a single table in your database that stores the current identity value. This allows LightSpeed to secure a block of identities and use them to set the identity value of newly created entities in your system. This works because every identity in the database, even in different tables, is unique.  KeyTable works with numeric identity types (Int32 and Int64).

KeyTable is a great identity method if you need high performance from you database as LightSpeed does not need to flush new entities to obtain identity values. That means less round trips to the database and therefore a more speedy application.  KeyTable is also portable between database engines.  For these reasons, KeyTable is the default identity method in LightSpeed.

By default, KeyTable secures identity values in blocks of 10.  You can override this by setting LightSpeedContext.IdentityBlockSize in code, or the identityBlockSize attribute in configuration.  A large value means LightSpeed needs to consult the key table less often, which improves performance, but can result in ‘wasted’ Ids if few entities are added in each run of the program.

If you use the KeyTable method, you must create the key table in the database.  To do this, run the KeyTable.sql schema file for your database, which you can find in your LightSpeed install directory.

Sequence and MultiSequence Identity Generation

Sequence identity generation is similar to KeyTable, but is natively supplied by the database engine. This means using sequence identity generation is limited to databases that support it: Oracle and PostgreSQL.  Sequence identity generation has similar characteristics to KeyTable, but is not portable because many databases do not support sequence objects.

By default, Sequence and MultiSequence assume that the sequence increment is 10.  If the sequence increment amount is something other than 10, you must set the identity block size to the sequence increment amount.  To do this, set LightSpeedContext.IdentityBlockSize in code, or the identityBlockSize attribute in configuration.  If the identity block size is different from the sequence increment amount, this will cause errors.

If you use the Sequence method, you must create the sequence in the database.  To do this, run the Sequence.sql schema file for your database, which you can find in your LightSpeed install directory.  The provided Sequence.sql assumes the default identity block size of 10; if you change the identity block size you must change the sequence increment amount and vice versa.

If you use the MultiSequence method, you must create each of the sequences you intend to use.  MultiSequence is typically used with existing databases with a ‘sequence per table’ policy, in which case the sequences will presumably already exist.

If you use MultiSequence, you must also implement a naming strategy to tell LightSpeed which sequence to use for each table.  Your naming strategy class must implement the INamingStrategy interface, and return the per-table name from GetMultiSequenceName.  All other members can return the default name.

public class SequencePerTableNamingStrategy : INamingStrategy
{
  public string GetMultiSequenceName(string defaultName, Type entityType)
  {
    // Example naming convention: seq_employee_ids, seq_order_ids, etc.
    return "seq_" + entityType.Name.ToLower() + "_ids";
  }
  // other members can all return defaultName (unless using a custom mapping policy)
}

See Defining Your Own Mapping Convention above for how to make LightSpeed use your naming strategy.

Guid and GuidComb Identity Generation

GUID identities are great for systems that require strong uniqueness (for example, to support replication) and are also extremely practical from a generation perspective, because LightSpeed never needs to consult the database to allocate an Id. However, while this is not an issue for many systems, GUIDs can have an impact on application aesthetics. For example, you may not want GUIDs present in your URLs if you are developing a web application.  Obviously, GUID methods work only with the Guid identity type.

The Guid identity method uses the .NET GUID generator to generate Ids.

The GuidComb identity method uses an alternative algorithm, described in Jimmy Nilsson’s article The Cost of GUIDs as Primary Keys, to trade randomness for database INSERT performance.  The COMB method generates GUIDs in a way that reduces the indexing cost when a new primary key value is inserted.  Consider GuidComb if you want GUID Ids, have a very large data set and are performing a lot of inserts.

IdentityColumn Identity Generation

Identity Column identity generation supports auto-incrementing identity columns. Identity Column identity generation is limited to databases that support auto-increment columns: SQL Server, SQL Server Compact, VistaDB, PostgreSQL, MySQL and SQLite.  Identity Column works with numeric identity types (Int32 and Int64).

This approach, while supported, is not a recommended method for identity generation.  Using this identity type means that LightSpeed cannot do optimized batching as well as it can with other identity methods. This is because LightSpeed needs to obtain the new identity value and update any in-memory associated objects before continuing the flush process.  Consequently, the IdentityColumn method should be used only with legacy databases where the auto-increment columns cannot be converted to normal columns, for example because of other applications that rely on the auto-increment behaviour.

Identity Generation Options

Some identity methods can be configured in different ways.  To specify configuration options for an identity method, set the LightSpeedContext.IdentityMethodOptions property.  The options object must be appropriate to the identity method in use.

At the time of writing, the only identity methods with configuration options are Sequence and MultiSequence, which can be configured using a SequenceIdentityMethodOptions object.  See the SequenceIdentityMethodOptions class documentation in the API reference for information.

How Block Allocation Methods Work

The KeyTable and sequence identity methods reserve Ids in blocks to minimise the number of database queries required to allocate Ids.  Block reservations are held at the LightSpeedContext instance level.  This means that clients that share the same LightSpeedContext instance will get Ids from the same reserved pool, whereas clients that create different LightSpeedContext instances will have their own pools.

For example, suppose you are writing a Web application.  You might choose to create a LightSpeedContext per page.  This would be wasteful because each page that performed even a single insert would reserve 10 Ids from the KeyTable or sequence, incurring its own database call.  By contrast, if you created a singleton LightSpeedContext, for example as a static field in Global.asax.cs, and each page referred to that one context, then the first page that performed an insert would cause 10 Ids to be reserved, incurring a database call, but subsequent pages would receive Ids from that reserved block (until it ran out), avoiding further database calls and improving performance.

In general, therefore, you should have a single LightSpeedContext instance per application (or one per database, if your application uses multiple databases).  Of course you can and often will create multiple independent units of work from this one context – in the Web example you would have a unit of work per Web request, even though the units of work shared the same LightSpeedContext object.