CODEWITHSUNDEEP
c#linq
user687554
user687554

Reputation: 11131

Writing to the database from LINQ in C#

I am trying to write some content to my database via LINQ. Currently, I'm getting the following error:

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

I understand this is a primary key related issue. My table has the identity specification set to "Yes" with Identity Increment set to "1". But I have successfully updated other tables using code that looks like the following:

using (DataModelDataContext context = new DataModelDataContext())
{
  List<MyTable> newRecords = new List<MyTable>();
  MyTable record1 = new MyTable();
  record1.CreatedBy = GetUserName();
  record1.Information = GetInformation();
  newRecords.Add(record1);

  MyTable record2 = new MyTable();
  record2.CreatedBy = GetUserName();
  record2.Information = GetInformation();
  newRecords.Add(record2);

  context.MyTables.InsertAllOnSubmit(newRecords);
  context.SubmitChanges();
}

What would cause this error? If I want to write new records, is there a way to set the primary key before it gets sent to the db? Something like "GetNextKey()" or something? Basically, I just need to insert these records. What am I doing wrong?

Thanks!

Upvotes: 0

Views: 979

Answers (3)

Keith
Keith

Reputation: 5381

Check to make sure your primary key/identity column has the following set within the DBML:

  • Auto Generated Value: True
  • Auto-Sync: OnInsert

This will make sure that LINQ-To-SQL will not try and insert the key value into that column and that it will update the value after an insert takes place. These should be on when you added the table to the DBML.

Upvotes: 0

KeithS
KeithS

Reputation: 71573

If the ID is important (say you're importing data, and records you'll be inserting later will reference these records), then turn off the identity temporarily while you perform the insert. If having a particular ID on each record doesn't matter, and you can let the identity column pick one (which is 99.9 percent of the time), make sure you're not specifying a value for the ID before trying to save the record. Also make sure that your mapping between the object and the DB specifies that the ID is an identity, so EF knows not to try to insert the ID column.

Upvotes: 0

Andrew Barber
Andrew Barber

Reputation: 40150

The code you have posted would work, assuming neither of those fields have the identity value. You basically need to assure you do not try to set the identity value before you add the item to the Table collection. (actually, you should never set the identity value from your code in most circumstances when it is to be generated in the DB).

So somewhere, the code that is having an error is trying to set the identity field's value.

Upvotes: 0

Related Questions