Using the Hi / Lo algorithm to generate keys in the Entity Framework Core

The Hi / Lo algorithm is useful when you need unique keys. In short, the Hi / Lo algorithm describes the mechanism for generating secure identifiers on the client side and not in the database ( safe in this context means no collisions ). It sets unique identifiers to the rows of the table, not depending on whether the row is immediately stored in the database or not. This allows you to immediately use identifiers, as normal sequential database identifiers.

Database sequences are cached, scaled and solve concurrency problems. But for each new value of the sequence, we constantly need to contact the database. And when we have a large number of inserts, it becomes a bit expensive. Therefore, to optimize work with sequences, use the Hi / Lo algorithm. EntityFramework Core supports Hi / Lo out of the box using ForSqlServerUseSequenceHiLo method

How does hi / lo work

Let's start with what Hi / Lo is . The basic idea is that you have two numbers to make up a primary key - a “high” ( high ) number and a “low” ( low ) number. The client can basically increase the “high” sequence, knowing that he can safely generate keys from the entire range of previous “high” values ​​with many “low” values.
')
For example, suppose you have a “high” sequence with a current value of 35, and a “low” number is in the range of 0-1023. The client can then 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 retrieving 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 making any inserts, which simplifies their batching.

Use Hi / Lo to generate keys in the Entity Framework Core

Let's take a look at how to use Hi / Lo to generate keys using the Entity Framework Core. Suppose 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 < >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 onnString = @"Server=localhost\SQLEXPRESS01;Database=EFSampleDB;Trusted_Connection=true;"; optionBuilder.UseSqlServer(onnString); } 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; } } 

DbContext consists of:

• the SampleDBContext constructor SampleDBContext which is the implementation of the DropCreateDatabaseAlways database DropCreateDatabaseAlways ;
• OnConfiguring method for configuring DBContext;
• The OnModelCreating method is the place where you can define the configuration of the model. To determine the Hi / Lo sequence, use the ForSqlServerUseSequenceHiLo extension ForSqlServerUseSequenceHiLo .

Run the application. As a result, the base “EFSampleDB” with the table “Category” and with the sequence “EntityFrameworkHiLoSequence” should be created. The name of the sequence and the scheme in which it will be created can be passed as a parameter:

ForSqlServerUseSequenceHiLo(string name, string schema) .



The “EntityFrameworkHiLoSequence” creation script looks as follows

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

This sequence starts at 1 and increases by 10. There is a difference between the Sequence and the Hi / Lo Sequence with respect to the INCREMENT BY option. In Sequence INCREMENT BY adds the value to the previous value of the sequence to generate a new value. Thus, 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, the INCREMENT BY parameter denotes the block value, this means that the next sequence value will be selected after first use 10.

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(); } 

As soon as this code falls into the line where the category is added to DBContext, the database is called to get the value of the sequence. This can be verified using SQL Server Profiler.



When dataContext.SaveChanges(); is called dataContext.SaveChanges(); All 3 categories will be saved with primary key values ​​that are already generated and are selected only once.



The sequence value will not be selected from the database until the Lo part is exhausted (10 records in our case), only when adding the 11th record, the database will be called to get the next sequence value (Hi).

You can create one sequence of several tables:

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

Hi / Lo Sequence Setup

There are no parameters in the ForSqlServerHasSequence method to change the initial value and the increment value. But you can specify these values ​​as follows, first we define the sequence with the StartAt and IncrementBy parameters and then use the same ForSqlServerUseSequenceHiLo extension ForSqlServerUseSequenceHiLo .

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

In this case, the sql script for DBSequenceHiLo will be as follows

 CREATE SEQUENCE [dbo].[DBSequenceHiLo] AS [int] START WITH 1000 INCREMENT BY 5 MINVALUE -2147483648 MAXVALUE 2147483647 CACHE GO 

Now, when we add three categories, the key value starts at 1000. And since the IncrementBy parameter is set to “5,” 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, then on the screenshot you can see the second database call.



Thanks for attention!