Home .NET Using the Hi/Lo algorithm for key generation in Entity Framework Core

Using the Hi/Lo algorithm for key generation in Entity Framework Core

by admin

Using the Hi/Lo algorithm for key generation in Entity Framework Core
Hi/Lo algorithm is useful when you need unique keys.In short, the Hi/Lo algorithm describes a mechanism for generating secure identifiers on the client side rather than in the database ( secure in this context means no collisions ).It sets unique identifiers to table rows, regardless of whether the row is immediately stored in the database or not.This allows the identifiers to be used immediately, like regular sequential database identifiers.
Sequences ( sequences ) databases are cached, scaled, and solve concurrency problems.But for every new sequence value, we constantly need to access the database. And when we have a large number of inserts, it becomes a bit overpriced. Therefore, to optimize work with sequences we use Hi/Lo algorithm. EntityFramework Core supports Hi/Lo "out of the box" using the method ForSqlServerUseSequenceHiLo

How Hi/Lo works

Let’s start with What is Hi/Lo The basic idea is that you have two numbers to make up the primary key, the "high"( high ) number and the "low"( low ) number. The client can basically increment the "high" sequence, knowing that it can safely generate keys from the entire range of the previous "high" value with multiple "low" values.
For example, suppose you have a "high" sequence with a current value of 35, and the "low" number is in the range 0-1023. Then the client can increase the sequence to 36 (for other clients to be able to generate keys when using 35) and know that the keys 35/0, 35/1, 35/2, 35/3… 35/1023 are all available.
It can be very useful (especially with ORM) to be able to set primary keys on the client side instead of inserting values without primary keys and then extracting them back to the client. Among other things, this means that you can easily create relationships between parents and children and have all the keys in place before you do any insertions, which makes it easier to batch them( batching ).

Using Hi/Lo to generate keys in Entity Framework Core

Let’s see how to use Hi/Lo to generate keys using Entity Framework Core. Let’s say we have a simple Category model :

public class Category{public int Id{ get;set; }public string Name { get; set; }}

Remember that EF configures the property Id or <type name> Id as a key. Now we need to create a DBContext:

public class SampleDBContext: DbContext{public SampleDBContext(){Database.EnsureDeleted();Database.EnsureCreated();}protected override void OnConfiguring(DbContextOptionsBuilder OptionBuilder){string_connstring = @Server=localhostSQLEXPRESS01;Database=EFSampleDB;Trusted_Connection=true;";optionBuilder.UseSqlServer(with String);}protected override void OnModelCreating(ModelBuilder modelBuilder){var entityTypeBuilder = modelBuilder.Entity<Category> ();entityTypeBuilder.ToTable("Category");entityTypeBuilder.HasKey(t => t.Id);entityTypeBuilder.Property(t => t.Id).ForSqlServerUseSequenceHiLo();entityTypeBuilder.Property(t => t.Name).HasMaxLength(128).IsRequired();}public DbSet<Category> Category { get; set; }}

The DbContext consists of :

  • constructors SampleDBContext which is an implementation of the database initializer DropCreateDatabaseAlways ;
  • methods OnConfiguring for configuring DBContext;
  • methods OnModelCreating – is the place to define the model configuration. To define the Hi/Lo sequence, use the extension method ForSqlServerUseSequenceHiLo

Now let’s run the application. As a result, the database "EFSampleDB" with the table "Category" and the sequence "EntityFrameworkHiLoSequence" should be created. The name of the sequence and the scheme it will be created in can be passed as a parameter :
ForSqlServerUseSequenceHiLo(string name, string schema)
Using the Hi/Lo algorithm for key generation in Entity Framework Core
The script for creating the "EntityFrameworkHiLoSequence" looks like this

CREATE SEQUENCE [dbo].[EntityFrameworkHiLoSequence]AS [bigint]START WITH 1INCREMENT BY.10MINVALUE -9223372036854775808MAXVALUE 9223372036854775807CACHEGO

This sequence starts with 1 and increases by 10. There is a difference between Sequence and Hi/Lo Sequence regarding the option INCREMENT BY In Sequence INCREMENT BY adds a value to the previous value of the sequence to generate a new value. So, in this case, if your previous sequence value is 11, then the next sequence value will be 11 + 10 = 21. In the case of Hi/Lo Sequence. parameter INCREMENT BY denotes a block value, this means that the next sequence value will be selected after the primary 10 is used.
Let’s add some data to the database :

using (var dataContext = new SampleDBContext()){dataContext.Category.Add(new Category { Name = "Name-1" });dataContext.Category.Add(new Category { Name = "Name-2" });dataContext.Category.Add(new Category { Name = "Name-3" });dataContext.SaveChanges();}

Once this code gets to the line where the category is added to the DBContext, a database call is made to get the sequence value. This can be checked with the SQL Server Profiler.
Using the Hi/Lo algorithm for key generation in Entity Framework Core
When called dataContext.SaveChanges(); all 3 categories will be saved with primary key values that are already generated and only selected once.
Using the Hi/Lo algorithm for key generation in Entity Framework Core
The sequence value will not be selected from the database until the Lo part is exhausted (10 records in our case), only when the 11th record is added, the database will be called to get the next sequence value (Hi).
You can create one sequence of multiple tables :

protected override void OnModelCreating(ModelBuilder modelBuilder){modelBuilder.ForSqlServerUseSequenceHiLo("DBSequenceHiLo");}

Hi/Lo Sequence Setting

In method ForSqlServerHasSequence doesn’t have any parameters to change the initial value and increment value. But you can specify these values as follows, first define a sequence with the parameters StartAt and IncrementBy and then use the same extension method ForSqlServerUseSequenceHiLo

modelBuilder.HasSequence<int> ("DBSequenceHiLo").StartsAt(1000).IncrementsBy(5);modelBuilder.ForSqlServerUseSequenceHiLo("DBSequenceHiLo");

In this case the sql-script for DBSequenceHiLo will look like this


Now when we add the three categories, the key value starts at 1000. And since the parameter. IncrementBy is set to "5", then when the sixth entry is added to the context, a query will be made to the database to get the next sequence value. If you change the code, and add 3 more categories, you can see the second database call in the screenshot.
Using the Hi/Lo algorithm for key generation in Entity Framework Core
Thank you for your attention!

You may also like