How to force PrimaryKey on table

ServiceStack.OrmLite
1

For my tests I need to populate the database with known data.

I need to force the Id (primary key) of tables.

Searching the forum I find two method:
a. typeof(LRAPaziente).GetModelMetadata().PrimaryKey.AutoIncrement = false
// insert
typeof(LRAPaziente).GetModelMetadata().PrimaryKey.AutoIncrement = true

b. ModelDefinition md = typeof(LRAPaziente).GetModelMetadata();
var tableName = db.GetDialectProvider().GetQuotedTableName(md);
db.ExecuteSql($“SET IDENTITY_INSERT {tableName} OFF”);
// insert
db.ExecuteSql($“SET IDENTITY_INSERT {tableName} ON”);

I discard the latter as I need to deploy on SQLServer, PostgreSQL and Oracle, so the first is the better approach for me but it does not work.

I’m using SQLServer and it throws the error when IDENTITY_INSERT is OFF it cannot insert value for primary key.This is the POCO:

public class LRAPaziente : DBObject, IHasId<int>
{
    [ApiMember(Description = Archivio.ID)]
    [Alias("IDAPAZIENTE")]
    [AutoIncrement]
    public int Id { get; set; }

    [ApiMember(Description = Archivio.PID)]
    [Alias("PID")]
    [StringLength(30)]
    [Index(Unique = true)]
    public string PID { get; set; }

    [ApiMember(Description = Archivio.COGNOME)]
    [Alias("COGNOME")]
    [StringLength(30)]
    public string Cognome { get; set; }

    [ApiMember(Description = Archivio.NOME)]
    [Alias("NOME")]
    [StringLength(30)]
    public string Nome { get; set; }

}

2

I need to force the Id (primary key) of tables.

OrmLite already treats Id fields as the primary key.

If you don’t want OrmLite to treat the Id field as an autoinremented PK then you can disable it with:

typeof(LRAPaziente).GetModelMetadata().PrimaryKey.AutoIncrement = false;

After which OrmLite will include the Id values when generating inserts, that’s all that’s needed to tell OrmLite to treat it as a normal PK.

3

typeof(LRAPaziente).GetModelMetadata().PrimaryKey.AutoIncrement = false;

is exactly what I’m trying to do, but I get the SQLServer error:

“Explict Value can’t be inserted in Table when IDENTITY_INSERT is OFF”

Maybe I’m missing something but I cannot figure out what.

4

If I switch to PostgreSQL all works fine, this is my code:

typeof(LRAPaziente).GetModelMetadata().PrimaryKey.AutoIncrement = false;

// insert

typeof(LRAPaziente).GetModelMetadata().PrimaryKey.AutoIncrement = true;

SQLServer instead complains. Any ideas?

5

This only configures OrmLite, you still need to configure your RDBMS to allow inserting values in AutoIncrementing Primary Keys columns. With SQL Server you’d need to disable IDENTITY_INSERT, i.e:

db.ExecuteSql($$"SET IDENTITY_INSERT {tableName} OFF");
6

Ok, I not realized that for SQLServer I have to force

db.ExecuteSql($“SET IDENTITY_INSERT {tableName} OFF”);

Now all work as expected.

Thanks.