Setting the identity seed for a Primary Key on a SQL Server table

ServiceStack.OrmLite
1

We need to set the identity seed for a table to start at 1000.

Currently I have the following attributes setup

[PrimaryKey]
[CustomField("INT IDENTITY(1000, 1)")]

This creates the table with the correct Identity Seed, but when inserting I get the following exception

Cannot insert explicit value for identity column in table 'TableName' when IDENTITY_INSERT is set to OFF

I’m assuming that because the property does not have an [AutoIncrement] attribute it’s trying to insert the value of the Id and then throwing a wobbly because it is an identity field.

If I add the [AutoIncrement] attribute then it errors on table creation, because it already has an identity field.

What would be the best way to acheive this?

2

The Error message suggests you need to explicitly disable IDENTITY_INSERT which you can do with something like:

var modelDef = typeof(Table).GetModelMetadata();
var tableName = db.GetDialectProvider().GetQuotedTableName(modelDef);
db.ExecuteSql($$"SET IDENTITY_INSERT {tableName} OFF");

//... Create Table

db.ExecuteSql($$"SET IDENTITY_INSERT {tableName} ON");
3

Unfortunately, this doesn’t fix the problem.

SET IDENTITY_INSERT is only valid for the session it is run in, so needs to be set before and after the INSERT statement, if this is done it just sets the Id to 0.

The problem is that the Id column of the table has the Identity set to true via the custom field ( INT IDENTITY(1000, 1) ), but because the column does not have the [AutoIncrement] attribute OrmLite is trying to execute an INSERT statement that includes the Id column, which in turn causes the error to be returned.

4

This is the solution I’ve come up with

var modelDef = typeof(TableType).GetModelMetadata();
var idFieldDef = modelDef.PrimaryKey;
     
if (idFieldDef.CustomFieldDefinition.Contains("IDENTITY") && !idFieldDef.AutoIncrement)
    {
                    using (var cmd = Db.CreateCommand())
                    {
                        var fields = OrmLiteUtils.GetNonDefaultValueInsertFields(table);
    
                        var newFields = fields.Where(field => field != idFieldDef.FieldName).ToList();
    
                        cmd.GetDialectProvider().PrepareParameterizedInsertStatement<TableType>(cmd, insertFields: newFields);
    
                        cmd.CommandText = cmd.CommandText.Replace("VALUES", "OUTPUT inserted.Id VALUES");
    
                        cmd.GetDialectProvider().SetParameterValues<TableType>(cmd, table);
    
                        var seededId = (int) cmd.ExecuteScalar();
                        return seededId ;
                    }
                }
5

Right, in that case an easier solution might be to execute a DDL statement, e.g:

var modelDef = typeof(TableType).GetModelMetadata();
var tableName = db.GetDialectProvider().GetQuotedTableName(modelDef);
db.ExecuteSql($$"DBCC CHECKIDENT ({tableName}, RESEED, 1000)");

Which you could also couple to the creation of the table using a Custom SQL Hook, e.g:

[PostCreateTable("DBCC CHECKIDENT (TableType, RESEED, 1000)")]
public class TableType
{
    [AutoIncrement]
    public int Id { get; set; }
}

db.CreateTable<TableType>();
6

Thanks that works, the only problem is that Db.Insert returns 1 not 1000. Checking the database the Id has been set to 1000

7

Setting selectIdentity: true fixed that, so all good now. Once again thank you for your help