How to: Manage Auto-Incremental Columns in Firebird

The purpose of this topic is to illustrate how to manage auto-incremental columns in Firebird with Telerik Data Access. The autoinc mechanism in Firebird is based on generators and triggers. If you have a table in Firebird database whose primary key column is filled by a unique Firebird generator, Telerik Data Access can automatically detect that the table has a server side generated primary key mechanism.

Database First Scenario

Suppose, you have the following Firebird table, generator and trigger.

The table is named TASK and contains three columns.

The generator is used to populate automatically the primary key column (the ID column). For example:

CREATE GENERATOR TASK_SEQ;
SET GENERATOR TASK_SEQ TO 9999999;

And you have a before insert trigger that set the value of the ID column by using the created Firebird generator. For example:

CREATE TRIGGER TASKS_BI FOR TASKS
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
 if (NEW.ID IS NULL) THEN NEW.ID = GEN_ID(TASK_SEQ, 1);
END

When you create a new model, you need to specify in the configuration of the ID property that it is an identity property and uses the Autoinc key generator. This way, each time when you insert a new object runtime, Telerik Data Access will use the generated ID value to maintain the object identity. The ID column will be omitted from the generated insert statement and the value that has been generated by the generator will be obtained.

taskConfiguration.HasProperty(x => x.ID).IsIdentity(KeyGenerator.Autoinc).
    HasFieldName("_ID").WithDataAccessKind(DataAccessKind.ReadWrite).
    ToColumn("ID").IsNotNullable().HasColumnType("INTEGER").HasPrecision(0).
    HasScale(0);

taskConfiguration.HasProperty(x => x.ID).IsIdentity(KeyGenerator.Autoinc).
    HasFieldName("_ID").WithDataAccessKind(DataAccessKind.ReadWrite).
    ToColumn("ID").IsNotNullable().HasColumnType("INTEGER").HasPrecision(0).
    HasScale(0)

Telerik Data Access looks only for Before Insert triggers. All other types of triggers (After Insert, Before Update, After Update, Before Delete and After Delete) will be ignored, i.e. the Identity Mechanism will not be set to DatabaseServerCalculated and the Id column will be included in the generated insert statement).

Model First Scenario

If your entity has an integral (int, long, byte, short, uint, ulong, ubyte, ushort), double or float identity that is configured to be calculated by the database server, the generated DDL script will add all necessary elements in the database schema to have a working autoinc setup.

taskConfiguration.HasProperty(x => x.ID).IsIdentity(KeyGenerator.Autoinc).
    HasFieldName("_ID").WithDataAccessKind(DataAccessKind.ReadWrite).
    ToColumn("ID").IsNotNullable().HasColumnType("INTEGER").HasPrecision(0).
    HasScale(0);

taskConfiguration.HasProperty(x => x.ID).IsIdentity(KeyGenerator.Autoinc).
    HasFieldName("_ID").WithDataAccessKind(DataAccessKind.ReadWrite).
    ToColumn("ID").IsNotNullable().HasColumnType("INTEGER").HasPrecision(0).
    HasScale(0)

The generated generator and trigger will have the following name format: "Gen_TableName_ColumnName" and "Trg_TableName_ColumnName".

For example, if your entity is named TASK and the primary key property is named ID, the generated trigger and generator will be:

CREATE GENERATOR "Gen_TASK_ID";
SET GENERATOR "Gen_TASK_ID" TO 0;
CREATE TRIGGER "Trg_TASK_ID" FOR "TASK"
ACTIVE BEFORE INSERT POSITION 0 AS
BEGIN
IF (NEW."ID" IS NULL) THEN NEW."ID" = GEN_ID("Gen_TASK_ID", 1);
END;

If you change the configuration of the identity property from to the default one (no parameters are passed to the IsIdentity method) and migrate your database, Telerik Data Access will generate DDL for dropping the trigger only. Respectively, if you modify the Identity Mechanism of an entity from Default to DatabaseServerCalculated and migrate your database, Telerik Data Access will generate a trigger and a default generator. If a generator with the same name already exists, Telerik Data Access will use that generator in the trigger and will not attempt to create a new generator.

How to create/migrate the database based on your model is described in this article.