Exploring PostgreSQL Sequences With Entity Framework CORE

As with many relational database dialects, PostgreSQL allows you to create and use Sequences.

The goal of this article is to be able to use PostgreSQL sequences with EF core.

The sequence is a database functionality that is not tied to a specific table, but provides the generation of a certain sequence of numbers that can be used by several tables.

Usually, depending on the project, it carries the identity function in most cases, but it can also be used in the number generation process for many business columns.

Consider the following simple syntax for creating a sequence in PostgreSQL:

CREATE SEQUENCE IF NOT EXISTS MySimpleSequence
as int
INCREMENT BY 1
MINVALUE -2000000
MAXVALUE 20000000
START WITH 4
CYCLE

The "CREATE SEQUENCE" command is used to create a sequence. After naming the Sequence, it will be shown what type it will be. Specifying the type is optional.

INCREMENT - How much to increment the last value each time when the Sequence is called

MINVALUE - Minimum border value (optional)

MAXVALUE - Maximum border value (optional)

START – start sequence from the given number

CYCLE – Do you want to start the Sequence to repeat after reaching the maximum value?

Several additional configurations are also provided when creating a Sequence. You can learn them here.

Once the Sequence is created, we can find it in the Sequences section and view it with the following command:

Here is how to call and use the created Sequence:

Now, let's see how to configure sequences with EF core:

1. Let's create a new Console app by opening Visual Studio

2. install "Microsoft.entityframeworkcore", "Microsoft.EntityFrameworkCore.Tools" and "Npgsql.EntityFrameworkCore.PostgreSQL" according to the solution configuration below.

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net6.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>disable</Nullable>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="microsoft.entityframeworkcore" Version="7.0.4" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="7.0.4">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="7.0.3" />
  </ItemGroup>
</Project>

3. Create the Models folder and create the following model's

public class Contract {
    public int Id {
        get;
        set;
    }
    public int RefCode {
        get;
        set;
    }
    public string Info {
        get;
        set;
    }
}
public class Order {
    public int Id {
        get;
        set;
    }
    public int OrderNo {
        get;
        set;
    }
    public string Details {
        get;
        set;
    }
}

4. Let's create TestDbContext under a Database folder

When deriving from the DbContext class, we override the OnConfiguring method and give the Connection string directly. Since the main focus of this article is working with sequences, we are ignoring the BEST PRACTICE for now.

The main issue is when we override the OnModelCreating method.

Here we configure the Sequence and define which columns will use it.

protected override void OnModelCreating(ModelBuilder modelBuilder) {
    modelBuilder.HasSequence < int > ("MySimpleSequence").IncrementsBy(1).HasMin(-2000000).HasMax(2000000).StartsAt(4).IsCyclic();
    #region Contract config
    modelBuilder.Entity < Contract > ().Property(pm => pm.Id).UseIdentityAlwaysColumn(); // use ALWAYS db generated identity
    modelBuilder.Entity < Contract > ().Property(pm => pm.RefCode).HasDefaultValueSql("nextval('\"MySimpleSequence\"')");
    #endregion
    #region Order config
    modelBuilder.Entity < Order > ().Property(pm => pm.Id).UseIdentityAlwaysColumn();
    modelBuilder.Entity < Order > ().Property(pm => pm.OrderNo).HasDefaultValueSql("nextval('\"MySimpleSequence\"')");
    #endregion
}

The code example above shows that the same Sequence can easily be configured with the EF CORE fluent API.

The HasSequence method configures the creation of a Sequence.

HasDefaultValueSql method, in this context, is responsible for providing internal usage of Sequence with its call command.

We use the following commands to perform the migration:

1. add-migration Initial

If we look at the generated Migration file, we can see that the sequence creation and its usage are shown.

2. update-database

Now it is time to do some insert :

static void Main() {
    using(TestDbContext dbContext = new()) {
        dbContext.Contracts.Add(new Models.Contract {
            Info = "simple info"
        });
        dbContext.SaveChanges();
    }
}

Here is the result of the insert operation: