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.

1. 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).

2. 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).

3. 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.

4. 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.

5. 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.

6. 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:

C#csharp	Copy Code
public abstract class KeyGenerator { //...   internal protected KeyGenerator(DataContext dataContext, MetaDataMember identityDataMember){ this.DataContext = dataContext; this.IdentityDataMember = identityDataMember; } public virtual object Generate(){ throw new NotImplementedException(); } public virtual string GetDBGenerateText(){ throw new NotImplementedException(); } }

Visual Basic

Copy Code

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:

1. 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.
2. 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.

• '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:

C#csharp

Copy Code

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

Copy Code

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

1. 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

2. 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