Insert using Linq-to-SQL where IDENTITY_INSERT is set to OFF

1
votes

I'm trying to insert a new row into a table without inserting an identity. I'm just inserting to the other columns. I want the identity to be created automatically.

Simplified code:

Table<Class1> customers = db.GetTable<Class1>();
customers.InsertOnSubmit(new Class1 { Prop1 = "test });
try { db.SubmitChanges(); }
catch (ChangeConflictException ex) { }

I get the following error:

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

I assume Linq2SQL updates all columns automatically. But how do I get around it? I've even tried to just leave out that column from the class, but then Linq2SQL tells me that:

Can't perform Create, Update, or Delete operations on 'Table(Class1)' because it has no primary key.

1
sqlsql-serverlinq-to-sql

1 Answers

2
votes

Your property should have the following properties:

[Column(Storage="ColumnID", AutoSync=AutoSync.Always, DbType="Int NOT NULL 
     IDENTITY", IsDbGenerated=true)]
public int ColumnID

If your column table has the auto-identity in database, then try recreate or update your LINQ TO SQL DB Context.