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.

public class Category
{
    public int CategoryID { get; set; }
    public string CategoryName { get; set; }
}

public class Product
{
    public int ProductID { get; set; }
    public string ProductName { get; set; }
}

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.

public class SampleDBContext : DbContext
{
    public SampleDBContext()
    {
        Database.EnsureDeleted();
        Database.EnsureCreated();
    }
    protected override void OnConfiguring(DbContextOptionsBuilder optionbuilder)
    {
        string sConnString = @"Server=localhost;Database=EFSampleDB;Trusted_Connection=true;"
        optionbuilder.UseSqlServer(sConnString);
    }

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

    public DbSet<Product> Products { get; set; }
    public DbSet<Category> Categories { get; set; }
}

The SampleDBContext() constructor is an implementation of database initializer DropCreateDatabaseAlways.
OnConfiguring() method is used for configuring the DBContext.
OnModelCreating method is a place to define configuration for the model. To define HiLo Sequence, use ForSqlServerUseSequenceHiLo extension method. You need to supply the name of the sequence.

Run the application. And you should see “EFSampleDB” created with Categories and Products table. And with DBSequenceHiLo sequence.

Following is the create script of DBSequenceHiLo,

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

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

using (var dataContext = new SampleDBContext())
{
    dataContext.Categories.Add(new Category() { CategoryName = "Clothing" });
    dataContext.Categories.Add(new Category() { CategoryName = "Footwear" });
    dataContext.Categories.Add(new Category() { CategoryName = "Accessories" });
    dataContext.SaveChanges();
    dataContext.Products.Add(new Product() { ProductName = "TShirts" });
    dataContext.Products.Add(new Product() { ProductName = "Shirts" });
    dataContext.Products.Add(new Product() { ProductName = "Causal Shoes" });
    dataContext.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.

modelbuilder.Entity<Category>()
            .Property(o => o.CategoryID).ForSqlServerUseSequenceHiLo();

This code will create a new sequence with default name “EntityFrameworkHiLoSequence” as no name was specified. You can also have multiple HiLo sequences. For example,

protected override void OnModelCreating(ModelBuilder modelbuilder)
{
    modelbuilder.ForSqlServerUseSequenceHiLo("DBSequenceHiLo");
    modelbuilder.Entity<Category>()
            .Property(o => o.CategoryID).ForSqlServerUseSequenceHiLo();
}

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,

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

In this case, following is the script of DBSequenceHiLo.

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

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.