SQL Server Value Generation

This page details value generation configuration and patterns that are specific to the SQL Server provider. It's recommended to first read the general page on value generation.

IDENTITY columns

By convention, numeric columns that are configured to have their values generated on add are set up as SQL Server IDENTITY columns.

Seed and increment

By default, IDENTITY columns start off at 1 (the seed), and increment by 1 each time a row is added (the increment). You can configure a different seed and increment as follows:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .Property(b => b.BlogId)
        .UseIdentityColumn(seed: 10, increment: 10);
}

Inserting explicit values into IDENTITY columns

By default, SQL Server doesn't allow inserting explicit values into IDENTITY columns. To do so, you must manually enable IDENTITY_INSERT before calling SaveChanges(), as follows:

using (var context = new ExplicitIdentityValuesContext())
{
    context.Blogs.Add(new Blog { BlogId = 100, Url = "http://blog1.somesite.com" });
    context.Blogs.Add(new Blog { BlogId = 101, Url = "http://blog2.somesite.com" });

    context.Database.OpenConnection();
    try
    {
        context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT dbo.Blogs ON");
        context.SaveChanges();
        context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT dbo.Blogs OFF");
    }
    finally
    {
        context.Database.CloseConnection();
    }
}

Note

We have a feature request on our backlog to do this automatically within the SQL Server provider.

Sequences

As an alternative to IDENTITY columns, you can use standard sequences. This can be useful in various scenarios; for example, you may want to have multiple columns drawing their default values from a single sequence.

SQL Server allows you to create sequences and use them as detailed in the general page on sequences. It's up to you to configure your properties to use sequences via HasDefaultValueSql().