Use HiLo to generate keys with Entity Framework Core

Entity Framework Core supports different key generation strategies like identity, Sequence and HiLo. In my previous post, I talked about using SQL Server Sequence with EF Core to Create Primary Key. Database sequences are cached, scalable and address concurrency issues. But there would be a database round-trip for every new the sequence value. And in case of high number of inserts this becomes a little heavy. But you can optimize the sequence with HiLo pattern. And EF Core supports “HiLo” out of the box. So in this post, we will see how to use HiLo to generate keys with Entity Framework Core.

Use HiLo to generate keys with Entity Framework Core

To begin with, a little info about HiLo Pattern. HiLo is a pattern where the primary key is made of 2 parts “Hi” and “Lo”. Where the “Hi” part comes from database and “Lo” part is generated in memory to create unique value. Remember, “Lo” is a range number like 0-100. So when “Lo” range is exhausted for “Hi” number, then again a database call is made to get next “Hi number”. So the advantage of HiLo pattern is that you know the key value in advance. Let’s see how to use HiLo to generate keys with Entity Framework Core.

First, define the models. Here is code for 2 model classes. For demonstration, I created 2 models with no relationship.

Remember, EF Core by convention configures a property named Id or <type name>Id as the key of an entity. Now we need to create our DBContext. Add a new class file and name it SampleDBContext.cs and add the following code.

As you can see it starts with 1 and get increment by 10. There is a difference between a Sequence and HiLo Sequence with respect to INCREMENT BY option. In Sequence, INCREMENT BY will add “increment by” value to previous sequence value to generate new value. So in this case, if your previous sequence value was 11, then next sequence value would be 11+10 = 21. And in case of HiLo Sequence, INCREMENT BY option denotes a block value which means that next sequence value will be fetched after first 10 values are used.

Let’s add some data in the database. Following code first add 3 categories and calls SaveChanges() and then adds 3 products and calls SaveChanges().

When this code is executed for the first time and as soon as it hit the first line where “Clothing” category is added to DBContext, a database call is made to get the sequence value. You can also verify it via SQL Server Profiler.

And when the first dataContext.SaveChanges();, all 3 categories will be saved. The interesting part to look at the generated query. The primary key values are already generated and fetched only once.

And even when 3 products are inserted, the sequence value will not be fetched from database. It’s only when 10 records are inserted (the Lo part is exhausted), then only a database call will be made to get next (Hi Part) sequence value.

Using HiLo for single entity

The above code makes use of HiLo sequence in both the tables. If you want to have it only for a particular table, then you can use the following code.

And within the database, 2 sequences will be created. For category EntityFrameworkHiLoSequence will be used and for all other entities, DBSequenceHiLo will be used.

Configuring HiLo Sequence

Unlike ForSqlServerHasSequence, there are no options available to change start value and increment value. However, there is a way to define these options. First, define a sequence with StartAt and IncrementBy options and use the same sequence ForSqlServerUseSequenceHiLo() extension method. Like,

So when we execute the same code to insert 3 categories, then the key value will start from 1000.

And since the IncrementBy option is set to “5”, so when the 6th insert is added in the context, a database call will be made to get next sequence value. Following is the screen shot of SQL Server profiler for 3 inserts of categories and then 3 inserts of products. You can see the database call to get the next value of the sequence is 2 times.

That’s it.

Thank you for reading. Keep visiting this blog and share this in your network. Please put your thoughts and feedback in the comments section.

PS: If you found this content valuable and want to return the favour, then