Identity insert

ServiceStack.OrmLite SQL Server
1

Hi,

As listed on another thread, I using the below to try to insert an identity

if (account.Id != 0)
        {
            typeof(Account).GetModelMetadata().PrimaryKey.AutoIncrement = false;
            conn.SetIdentityInsert<Account>(true);
       }
 conn.Insert(account);

The SetIdentityInsert extension method is my own, but I get the following:

Insert Failure. Explicit value must be specified for identity column in table 'Account' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column..

So it appears the IDENTITY_INSERT is on, but OrmLite is not passing the Id into the insert statement.

My Primary Key property looks like:

    [Alias("AccountID")]
    [PrimaryKey]
    [AutoIncrement]
    public override long Id { get; set; }

Any ideas?

G

2

Whilst it’s not recommended to change metadata attributes at runtime like this, I’ve just tried it in this Gist which looks like it’s working as intended, e.g:

http://gistlyn.com/?gist=aa242a2bf9c6603ec391a86d9772e5eb

var newTodo = new Todo {
    Content = "Learn OrmLite",
    Order = 1
};
db.Insert(newTodo);

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

db.Insert(new Todo {
    Id = 10,
    Content = "Inserted With Id",
    Order = 1
});
=

Where OrmLite inserting the Id when AutoIncrement = false:

DEBUG: SQL: INSERT INTO "Todo" ("Content","Order","Done") VALUES (@Content,@Order,@Done)
PARAMS: @Content=Learn OrmLite, @Order=1, @Done=0
DEBUG: SQL: INSERT INTO "Todo" ("Id","Content","Order","Done") VALUES (@Id,@Content,@Order,@Done)
PARAMS: @Id=10, @Content=Inserted With Id, @Order=1, @Done=0

Can you show the SQL that’s generated?

2 Likes
3

Thanks for quick response. SQL below - no AccountId. I have tried updating your gist to be using a base Todo with same overriding Id and you’re right, it works as expected. I will continue to dig.

INSERT INTO “Account” (“Title”,“Initials”,“Firstname”,“Lastname”,“CompanyName”,“AccountsRef”,“ClubName”,“Address1”,“Address2”,“Town”,“County”,“Country”,“Postcode”,“BillingName”,“BillingAddress1”,“BillingAddress2”,“BillingTown”,“BillingCounty”,“BillingCountry”,“BillingPostcode”,“AccountType”,“Email”,“Notes”,“Mobile”,“Phone”,“WorkPhone”,“MarketingOptIn”,“SmsOptIn”,“EmailOptIn”,“ThirdPartyOptIn”,“Resident”,“Source”,“SourceRef”,“PreferredInvoiceFormat”,“RenewalToken”,“RegistrationStatus”,“UserId”,“Agent”,“AgentPhone”,“SiteId”,“LicenceNumber”,“DateOfBirth”,“ParentAccountId”,“CurrentOccupation”,“InsurancePolicy”,“LicenceEndorsements”,“LeadSource”,“LeadPrimaryInterest”,“LeadConversionDate”,“ArchiveReason”,“ArchiveReasonNotes”,“ArchiveDate”,“ArchivedBy”,“ArchivedByName”,“DebtManagementStage”,“DebtType”,“Status”,“ExtendedData”,“DeletedBy”,“DeletedByName”,“DeletedOn”,“DeletionToken”,“CreatedOn”,“ModifiedOn”,“CreatedBy”,“ModifiedBy”,“Deleted”) VALUES (–params removed --); SELECT SCOPE_IDENTITY()

4

Me being an idiot - I was using automapping so the type wasn’t the type that i was setting the metadata on. Sorry for wasting your time.

1 Like
5

ok no worries, glad you’ve resolved the issue.