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.
![]() |
---|
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: |
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:
The properties of the TableHiLo generator are:
SequenceHiLo. The same as TableHiLo, but uses a sequence instead of a table. Has only two properties:
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.
A custom ID generator should inherit from the Devart.Data.Linq.Common.KeyGenerators.KeyGenerator abstract class:
C#csharp | ![]() |
---|---|
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 | ![]() |
---|---|
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:
'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 | ![]() |
---|---|
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], ... );
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