Use SQL Server Sequence in Entity Framework Core to Create Primary Key

Entity Framework doesn’t support Sequence out of the box. However, there are ways to use it. But the good news is, Entity Framework Core supports Sequence out of the box. I also mentioned in my earlier post Quick summary of what’s new in Entity Framework Core 1.0. So in this post, let’s find out how to use SQL Server Sequence in Entity Framework Core to create primary key.

Use SQL Server Sequence in Entity Framework Core

To begin with, a little info about Sequence. SEQUENCE was introduced in SQL Server 2012. Sequence is a user-defined object and it generates a sequence of numeric values according to the properties with which it is created. It is similar to Identity column, but there are many differences between them. Like,

• The Sequence is used to generate database-wide sequential number, where identity column is tied to a table.
• Sequence is not associated with a table. You can associate it with multiple tables.
• It can be used in insert statement to insert identity values, it can also be used in T-SQL Scripts.

I suggest you to read this and this post to find out the difference between Identity and Sequence in SQL Server.

Now coming to our topic, let see how to use SQL Server Sequence in Entity Framework Core to create primary key. First, define the model. Here is code for the model class.

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

EF Core by convention configures a property named Id or <type name>Id as the key of an entity. Note, “Microsoft.EntityFrameworkCore.SqlServer” nuget package needs to be included to use SQL Server with EF Core.

Now we need to create our DBContext. Add a new class file and name it SampleDBContext.cs and add 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.ForSqlServerHasSequence<int>("DBSequence")
                              .StartsAt(1000).IncrementsBy(2);
       modelbuilder.Entity<Category>()
          .Property(x => x.CategoryID)
          .HasDefaultValueSql("NEXT VALUE FOR DBSequence");
    }

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

• The SampleDBContext() constructor is an implementation of database initializer DropCreateDatabaseAlways.
• OnConfiguring() method is used for configuring the DBContext. Here we are saying that use SQL database and define the connection string to connect.
• OnModelCreating method is used to define configuration for the model. The ForSqlServerHasSequence extension method is used for creating SQL Server Sequence. You need to supply the name of the sequence. Additionally, you can also configure start number and increment option. There is also a HasSequence extension method which is similar to ForSqlServerHasSequence. The reason for having these 2 different sets of method is, EF Core supports many database providers. And functionality for these providers are slightly different so you are able to specify a different behavior for the same property depending on the provider being used. Read this to find out more.
• Once the sequence is defined, then we need to configure it for the model. As the primary key column is no more identity column, so while inserting EF needs to know the primary key column value. The HasDefaultValueSql extension method can be used to provide a default value for any column. So in this case, the default value we need to get it from the newly created sequence. The SQL Syntax to select next value from the sequence is,

  SELECT NEXT VALUE FOR DBSequence

  And same syntax we need to supply HasDefaultValueSql except “SELECT” keyword.

Now, lets add some data using the following code.

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

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

Following is the create script of Categories table,

CREATE TABLE [dbo].[Categories](
   [CategoryID] [int] NOT NULL DEFAULT (NEXT VALUE FOR [DBSequence]),
   [CategoryName] [nvarchar](max) NULL,
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED 
(
   [CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

And following is the create script of DBSequence,

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

And following is the result of Select * from Categories query.

That’s it. You can use sequence for any of your columns. EF Core also supports HiLo Pattern to generate keys and HiLo pattern also makes use of sequence. Read how to use HiLo to generate keys with Entity Framework Core.

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