Service Stack OrmLite and Identity_Insert

Question

When using Service Stack OrmLite how do you insert identity values exactly?

For instance in SQL Server when Identity_Insert is turned on for a table the identity value will be inserted exactly as specified and will not instead be auto generated.

Answer 1

1. Do not decorate your primary key with the [AutoIncrement] attribute. If you do so, then OrmLite will leave that column name and value out of the INSERT statement.
2. Issue the SET IDENTITY_INSERT statement. Make sure to let OrmLite build the table name for you, taking into account any [Schema] and [Alias] attributes.

For example:

public void InsertAll(IEnumerable<TTable> set)
{
    const string identity = "SET IDENTITY_INSERT {0} {1}";
    var schema = typeof(TTable).FirstAttribute<SchemaAttribute>();
    var tableName = typeof(TTable).FirstAttribute<AliasAttribute>();
    var qualified = (schema == null ? "dbo" : schema.Name) + "." +
                    (tableName == null ? typeof(TTable).Name : tableName.Name);
    using (var db = _dbConnectionFactory.OpenDbConnection())
    {
        try
        {
            db.ExecuteSql(string.Format(identity, qualified, "ON"));
            db.InsertAll(set);
        }
        finally
        {
            db.ExecuteSql(string.Format(identity, qualified, "OFF"));
        }
    });
}