Primary Key Generation

In This Topic

LinqConnect Documentation
Primary Key Generation
[email protected]

To better support the object-oriented approach of working with data and speed up accessing these data, LinqConnect caches entity objects (object caching and tracking is discussed in details in the corresponding topic). The key used for this caching is the entity key, which in turn typically is the primary key of the corresponding table in the database. Even if the table has no primary key, the entity type should, or the LinqConnect runtime won't be able to differ (and thus insert, update or delete) entity objects.

Thus, it is necessary to set a distinct key value for each new entity being inserted. Apparently, this task is at least tedious if it is left for the developer: first, it is necessary to generate the value either in the application or at the server. Both ways mean that you have to write either scripts to create sequences and triggers, or queries that get, e.g., the largest ID already used in this particular table. Second, if you generate the value at the server, you again write queries that get it back to your entity object. Surely, these manipulations would be quite far from the very ORM idea. To solve this task with minimal efforts, LinqConnect provides you a wide set of key generation algorithms. We describe them below.

Identity. This is the simplest (from the developer's point of view) approach: it is supposed that the key value is generated at the server, regardless of the exact way it is done - this may be a trigger, or the primary key column is auto-incremental, or it has a default value referencing to a sequence. Thus, the INSERT command is generated in such a way that no value is inserted into the ID column, and the column value generated by the server is returned back to your application. No additional queries or calculations are performed.

Just in case: the Identity generator does not necessarily mean that the primary key is auto-incremented; it can even be of a non-numeric data type. All the Identity generator means is that the generation of the new ID is completely handled by the server side when inserting the new row.

Note:
The 'default' way of the server-side PK generation (i.e., the one provided by the scripts created by the Update Database from Model and Generate Database Script wizards of Entity Developer) for integer fields is the following:

If the DBMS supports auto-incremental columns (e.g., SQL Server, MySQL, PostgreSQL and SQLite), the primary key column is created auto-incremental;
Otherwise (and at the moment this is the Oracle database only), Entity Developer creates a sequence and a trigger that replaces the primary key value by the next sequence value on each insert to the table. These sequence and trigger are named in the following way: 'AI$[TableName]_[PropertyName]_seq' and 'AI$[TableName]' respectively. This is done to exclude the possibility of creating database objects conflicting with the ones created manually. Entity Developer relies on these trigger and sequence when determining the differences between the model and the actual database schema (e.g., it considers the PK as not auto-incremented if cannot find the 'AI$...' trigger and sequence).

Sequence. This ID generator takes the next value from the specified sequence:

It is available only for DBMSs that support explicit use of sequences (i.e., Oracle and PostgreSQL).
TableHiLo. This generator implements the following approach: the server side assigns a range of primary key values for the client, and the client uses these values for new entities. The range is allocated in such way that other clients cannot use IDs from this range. Whenever the allocated range is exhausted, the application asks the server for additional IDs, and so on. The main advantage of this approach is that the application generates ID values on its own (with only a single round-trip to the server per a large number of new entities), at the same time being free of possible concurrency violations with other applications.

Precisely, the TableHiLo generator works in the following way:
- A table in the database stores a value called 'hi' in the row specific for the particular entity class.
- Each time a new client needs to generate a primary key for this entity class, this client gets the current 'hi' value and increments the value stored in the database. This way, each other client will get a different 'hi' value.
- Having the current 'hi' value, the client increments another value, called 'lo', from zero to some limit (say, 'max_lo'; this limit determines the range of the allocated interval of primary keys).
- ID for a new entity is defined as hi*(max_lo+1)+lo.
- Once 'lo' reaches the 'max_lo' value, the application calls to the server for the next 'hi' value.
The properties of the TableHiLo generator are:
- Table. Specifies the table where the 'hi' value is stored.
- Column. The column of this table which contains the 'hi' value.
- MaxLo. The maximal possible 'lo' value (and so the range of the ID set allocated by the server at once).
- KeyField. If the generator is used for multiple entity types, it would be a waste to have a separated 'hi' table for each. And if the 'hi' table keeps the 'hi' values for multiple entity classes, the runtime has to have the possibility to differ them. The KeyField is the name of the column, by which LinqConnect should obtain the row assigned for this particular entity type. If this property is not specified, LinqConnect simply takes the first row from the 'hi' table.
- KeyFieldValue. Specifies the value of the KeyField column that corresponds to the particular entity class.
SequenceHiLo. The same as TableHiLo, but uses a sequence instead of a table. Has only two properties:
- MaxLo. The maximal possible 'lo' value (and so the range of the ID set allocated by the server at once).
- Sequence. The sequence from where the application gets new 'hi' values.
Guid. This generator gets a new GUID for the primary key of each new entity. The obvious advantage of the method is that no round-trips to the server are needed at all. However, it may lead to a concurrency conflict or a constraint violation if two clients accidentally generate the same GUID, or the generated GUID is already a primary key of some existing row.
Custom. Quite obvious from the name, this is a custom generator implemented in your code. See below how to create and use such generators.

Implemeting a Custom Key Generator Class

A custom ID generator should inherit from the Devart.Data.Linq.Common.KeyGenerators.KeyGenerator abstract class:

Visual Basic

Public MustInherit Class KeyGenerator
    '...
 
    Protected Friend Sub New(dataContext As DataContext, identityDataMember As MetaDataMember)
        Me.DataContext = dataContext
        Me.IdentityDataMember = identityDataMember
    End Sub
 
    Public Overridable Function Generate() As Object
        Throw New NotImplementedException()
    End Function
 
    Public Overridable Function GetDBGenerateText() As String
        Throw New NotImplementedException()
    End Function

End Class

Two methods of the abstract class correspond to two different modes of key generation:

The Generate() methods creates and returns a new key value. Thus, it is called before executing the insert command, and the generated key is used as a parameter of this command.
The GetDbGenerateText() method returns the SQL statement that creates the new key value at the server side. It is directly substituted into the insert command.

Which of these modes is used depends on the IsDbGenerated property of the entity field:

'false' means that the key is created via Generate() (i.e., at the client side and before inserting the row). For example, the 'HiLo' generators work in this way. The corresponding SQL will look like

SELECT product_hilo_sequence.NEXTVAL FROM dual

INSERT INTO "Products" ("ProductName", ..., "ProductID") VALUES (:p1, ..., :p9)
-- p1: Input VarChar (Size = 36, DbType = String) [Twain Mark. Adventures of Tom Sawyer]
...
-- p9: Input Number (Size = 0, DbType = Decimal) 9001

'true' means that GetDBGenerateText() is used (thus, the key is created at the server right when executing the insert). For example, the Sequence generator may work in this mode. The corresponding insert command would be

INSERT INTO "Products" ("ProductName", ..., "Discontinued", "ProductID") VALUES (:p1, ..., :p8, PRODUCT_SEQUENCE.NEXTVAL)
-- p1: Input VarChar (Size = 36, DbType = String) [Twain Mark. Adventures of Tom Sawyer]
...
-- p8: Input Number (Size = 0, DbType = Decimal) 0

Besides the key generation methods, a custom generator has to have a public constructor. The constructor should have two mandatory parameters and any number of optional parameters. The mandatory parameters are instances of Devart.Data.Linq.DataContext and Devart.Data.Linq.Mapping.MetaDataMember, the latter representing the entity member the constructor is set for. The DataContext object can be used, e.g., to instantiate a proper connection if the generator should query the server, and MetaDataMember allows getting the information like the names of the table and key column. The optional parameters should be of the string type (so that they should be parsed inside the generator constructor). The main reason for this is that there should be a possibility of specifying these parameters in XML mapping files.

As an example, the following class generates new ID as an increment of the largest ID already used in the table:

public class MaxIdGenerator : KeyGenerator
{
 
    public string ConnectionString { get; private set; }
    public string Table { get; private set; }
    public string Column { get; private set; }
    public int Step { get; private set; }
 
    public MaxIdGenerator(DataContext context, MetaDataMember idMember, string step)
        : base(context, idMember)
    {
 
        this.ConnectionString = context.Connection.ConnectionString;
        this.Table = idMember.DeclaringType.TableName;
        this.Column = idMember.MappedName;
        this.Step = (int)Convert(typeof(int), step);
    }
 
    public override object Generate()
    {
 
        int maxId = 0;
        using (OracleConnection conn = new OracleConnection(this.ConnectionString))
        {
 
            conn.Open();
            OracleCommand cmd = new OracleCommand(
              String.Format("select max({0}) from {1}", this.Column, this.Table),
              conn);
            OracleDataReader reader = cmd.ExecuteReader();
            reader.Read();
 
            maxId = reader.GetInt32(0) + this.Step;
        }
 
        return maxId;
    }
 
    public override string GetDBGenerateText()
    {
 
        return String.Format(
          "(select max({0}) from {1}) + {2}",
          this.Column,
          this.Table,
          this.Step
        );
    }
}

Visual Basic

Public Class MaxIdGenerator
    Inherits KeyGenerator
 
    Public Property ConnectionString() As String
        Get
            Return m_ConnectionString
        End Get
        Private Set(value As String)
            m_ConnectionString = value
        End Set
    End Property
    Private m_ConnectionString As String
    Public Property Table() As String
        Get
            Return m_Table
        End Get
        Private Set(value As String)
            m_Table = value
        End Set
    End Property
    Private m_Table As String
    Public Property Column() As String
        Get
            Return m_Column
        End Get
        Private Set(value As String)
            m_Column = value
        End Set
    End Property
    Private m_Column As String
    Public Property [Step]() As Integer
        Get
            Return m_Step
        End Get
        Private Set(value As Integer)
            m_Step = value
        End Set
    End Property
    Private m_Step As Integer
 
    Public Sub New(context As DataContext, idMember As MetaDataMember, [step] As String)
        MyBase.New(context, idMember)
 
        Me.ConnectionString = context.Connection.ConnectionString
        Me.Table = idMember.DeclaringType.TableName
        Me.Column = idMember.MappedName
        Me.[Step] = CInt(Convert(GetType(Integer), [step]))
    End Sub
 
    Public Overrides Function Generate() As Object
 
        Dim maxId As Integer = 0
        Using conn As New OracleConnection(Me.ConnectionString)
 
            conn.Open()
            Dim cmd As New OracleCommand([String].Format("select max({0}) from {1}", Me.Column, Me.Table), conn)
            Dim reader As OracleDataReader = cmd.ExecuteReader()
            reader.Read()
 
            maxId = reader.GetInt32(0) + Me.[Step]
        End Using
 
        Return maxId
    End Function
 
    Public Overrides Function GetDBGenerateText() As String
 
        Return [String].Format("(select max({0}) from {1}) + {2}", Me.Column, Me.Table, Me.[Step])
    End Function
End Class

In the constructor of the MaxIdGenerator class, we collect all information necessary to create SQL statements used by the generator: the connection string used by the context, the table and column names, and the increment step that the generator should use (note that the step parameter is a string).

The Generate() method opens a connection to the database the current context operates with, and executes a command getting the maximal value available at the key column up to the moment.

The GetDBGenerateText() method creates a SQL statement doing the same as the Generate() method. As it was already said, the difference is that this SQL statement is explicitly used in the insert command (instead of executing 'select max...' first, and passing the generated key as a parameter).

Note that the sample is written for Oracle, and the GetDBGenerateText method may have to be modified or even cannot be implemented if you are working with some other DBMS. The reason is that some DBMSs does not support insert commands with subqueries, like

insert into MyTable values(
  (select max(Id) from MyTable) + 1,
  [value1],
  ...
);

Using a Custom Key Generator Class

After overriding the KeyGenerator methods and implementing a public constructor, you can use your class for primary key generation. To do so, set the IdGenerator property of the key entity field to Custom, and specify the full name of your class in the corresponding property of the generator:

Full name means the assembly qualified name here, since the LinqConnect runtime has to be able to find your class, while it knows nothing about your assembly(-ies).

If you use any optional parameters for your generator class, you should specify them and set their values in the Parameters collection. For example, we have to specify the Increment property for the MaxIdGenerator class:

Notice that the parameter values are strings, as, firstly, there should be a way to specify these values in XML mapping, and secondly, Entity Developer has no information about their actual types. Thus, the generator constructor has to implicitly convert these values (see the above Implementing a custom key generator class section).

Here are the samples of resulting SQLs we get when inserting a new Product object with MaxIdGenerator enabled

when IsDbGenerated is false:

select max("ProductID") from CRM_DEMO."Products"

INSERT INTO "Products" ("ProductName", ..., "ProductID") VALUES (:p1, ..., :p9)
-- p1: Input VarChar (Size = 52, DbType = String) [Ritter E.A. Shaka Zulu: The Rise of the Zulu empire.]
...
-- p9: Input Number (Size = 0, DbType = Decimal) 9002

when IsDbGenerated is true:

INSERT INTO "Products" ("ProductName", ..., "Discontinued", "ProductID")
  VALUES (:p1, ..., :p8, 
    (select max("ProductId") from CRM_DEMO."Products") + 1
  )
-- p1: Input VarChar (Size = 32, DbType = String) [Livius T. Ab Urbe condita Libri.]
...
-- p8: Input Number (Size = 0, DbType = Decimal) 0

[email protected]